Oracle ERP World: the collect function in 10g

Oracle 10g has introduced an extremely useful new group function, COLLECT. This function enables us to aggregate data into a collection, retaining multiple records of data within a single row (like a nested table). One of the main benefits of this function is that it makes "string aggregation" (one of the web's most-requested Oracle technique) very simple. This article will introduce the COLLECT function and then demonstrate how it can be used to aggregate multiple records into a single value (a technique known as "string aggregation").

an overview of the collect function

We'll start by demonstrating the COLLECT function. We'll run a simple query against the ubiquitous EMP table to collect the names of all employees by department.

SQL> SELECT deptno
  2  ,      COLLECT(ename) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- ------------------------------------------------------------------------------------
        10 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('CLARK', 'KING')
        20 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
        40 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('MILLER')

4 rows selected.

Something looks a little unusual here, but ignoring the strange identifier for a moment, we can see that the COLLECT function has aggregated the employee names per department as requested.

system-generated types

Moving on to the strange identifier in the example output, we can see that Oracle has created a collection type to support the COLLECT function. The behaviour is different between 10g releases 1 and 2, so we'll investigate each separately.

10g release 1

For our example EMP query above (executed in a 10.1 database), Oracle has created a supporting type named "SYSTPXeCjDqbWSqWrshgYrRPR4Q==". We can find this in the dictionary as follows.

SQL> SELECT owner
  2  ,      typecode
  3  FROM   all_types
  4  WHERE  type_name = 'SYSTPXeCjDqbWSqWrshgYrRPR4Q==';

OWNER                          TYPECODE
------------------------------ ------------------------------
SYS                            COLLECTION

1 row selected.

It appears as though Oracle generates a supporting collection type every time it hard-parses a SQL statement that uses the COLLECT function. In Oracle 10.1, the type is created in the SYS schema. If we try to use this new type as follows, we'll find that we cannot.

SQL> SELECT *
  2  FROM   TABLE(
  3            sys."SYSTPXeCjDqbWSqWrshgYrRPR4Q=="('A','B','C') );

          sys."SYSTPXeCjDqbWSqWrshgYrRPR4Q=="('A','B','C') )
          *
ERROR at line 3:
ORA-00904: "SYS"."SYSTPXeCjDqbWSqWrshgYrRPR4Q==": invalid identifier

In addition to the burden this must place on the parsing process, we might also be concerned about the number of system-generated types that might start appearing in our database (for reasons unknown, some DBAs and developers are worried about this sort of thing). If we flush the shared pool or even bounce the database, the type persists, even though it no longer supports a cached SQL statement. Oracle Support's official line on this (which used to be "bounce the database to remove the type") is that SMON cleans up unused types "after a period". Recent experience suggests that this period can be anything up to 24 hours after the bounce, so in an online database with a large shared pool, these types could stick around for some considerable period. Whether we choose to worry about this or not is another matter entirely!

10g release 2

In Oracle 10g Release 2, the type is created in the schema that parses the SQL statement, as we can see in the following example (we must first repeat the original EMP query to generate a type).

SQL> SELECT deptno
  2  ,      COLLECT(ename) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- ------------------------------------------------------------------------------------
        10 SYSTPo3itZvoiRAyeH+f5LKv6+Q==('CLARK', 'KING')
        20 SYSTPo3itZvoiRAyeH+f5LKv6+Q==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 SYSTPo3itZvoiRAyeH+f5LKv6+Q==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
        40 SYSTPo3itZvoiRAyeH+f5LKv6+Q==('MILLER')

4 rows selected.

SQL> SELECT owner
  2  ,      typecode
  3  FROM   all_types
  4  WHERE  type_name = 'SYSTPo3itZvoiRAyeH+f5LKv6+Q==';

OWNER                          TYPECODE
------------------------------ ------------------------------
SCOTT                          COLLECTION

1 row selected.

The fact that the parsing schema owns the collection type in Oracle 10.2 means that we can use these types if we wish, as follows.

SQL> SELECT *
  2  FROM   TABLE(
  3            "SYSTPo3itZvoiRAyeH+f5LKv6+Q=="('A','B','C') );

COLUMN_VAL
----------
A
B
C

3 rows selected.

Furthermore, removing this system-generated type is much more simple in 10.2 than in 10.1. Firstly, because we own the type, we can simply drop it as follows (assuming that the SQL statement that generated it is no longer required).

SQL> DROP TYPE "SYSTPo3itZvoiRAyeH+f5LKv6+Q==";

Type dropped.

Bear in mind, however, that this type is supporting a SQL cursor. Therefore, if we do decide to drop the system-generated type as above, the underlying SQL cursor will be removed from the shared pool. Therefore, a re-run of the original SQL statement will need to be hard-parsed and a new type will be created accordingly.

Alternatively, a bounce of the database will drop the type immediately (rather than at some point during the next 24 hours, which is the 10.1 behaviour). Despite this, we are probably best to leave the SQL cursor to age out of the shared pool naturally, leaving SMON to clean up at a later stage.

using our own collection types

It is possible to use our own collection types with COLLECT. The CAST function (available at least as far back as Oracle 8.0 and possibly further) can be used to turn the results of the COLLECT into a type of our choosing. Note that this doesn't stop Oracle creating system-generated types to support the SQL statement, but it does make the results easier to work with.

In the following example, we'll create a standard VARCHAR2 collection type and CAST the results of our collected employee names.

SQL> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> SELECT deptno
  2  ,      CAST(COLLECT(ename) AS varchar2_ntt) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- ---------------------------------------------------------------------
        10 VARCHAR2_NTT('CLARK', 'KING')
        20 VARCHAR2_NTT('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 VARCHAR2_NTT('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
        40 VARCHAR2_NTT('MILLER')

4 rows selected.

Note that if you are casting collections of numbers, Oracle can be particularly fussy about precisions and scales, as the following example demonstrates. We'll create a general collection of number and then attempt to cast a collection of employee salaries (the EMP.SAL column is defined as NUMBER(7,2)).

SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
  2  /

Type created.

SQL> SELECT deptno
  2  ,      CAST(COLLECT(sal) AS number_ntt) AS sals
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

,      CAST(COLLECT(sal) AS number_ntt) AS sals
            *
ERROR at line 2:
ORA-22814: attribute or element value is larger than specified in type

This is rather a confusing problem, as the unconstrained NUMBER type should easily incorporate a NUMBER(7,2). To wrap this up, however, there are two simple solutions. We can either make the collected column fit the type or the type fit the column, as shown below. First we'll make the column fit the type.

SQL> SELECT deptno
  2  ,      CAST(COLLECT(CAST(sal AS NUMBER)) AS number_ntt) AS sals
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO SALS
---------- -------------------------------------------------------------------------
        10 NUMBER_NTT(2450, 5000)
        20 NUMBER_NTT(800, 2975, 3000, 1100, 3000)
        30 NUMBER_NTT(1600, 1250, 1250, 2850, 1500, 950)
        40 NUMBER_NTT(1300)

4 rows selected.

Secondly we'll make the type fit the column.

SQL> CREATE TYPE number_7_2_ntt AS TABLE OF NUMBER(7,2);
  2  /

Type created.

SQL> SELECT deptno
  2  ,      CAST(COLLECT(sal) AS number_7_2_ntt) AS sals
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO SALS
---------- -------------------------------------------------------------------------
        10 NUMBER_7_2_NTT(2450, 5000)
        20 NUMBER_7_2_NTT(800, 2975, 3000, 1100, 3000)
        30 NUMBER_7_2_NTT(1600, 1250, 1250, 2850, 1500, 950)
        40 NUMBER_7_2_NTT(1300)

4 rows selected.

string aggregation using collect

We'll now see a practical demonstration of what the COLLECT function can be used for. One of the FAQs of Oracle developer forums is how to aggregate multiple strings into a single value. From releases of Oracle 8.0 onwards, there have been numerous methods for doing this. The most well-known method undoubtedly utilises Tom Kyte's "STRAGG" user-defined aggregate function. The STRAGG function is popular because it is extremely easy to use and faster than any pre-9i method. The COLLECT function, when combined with a function to turn the elements of a collection into a string, is faster still. We'll look at this below.

We'll continue with our standard VARCHAR2_NTT collection type, but we'll also require a "collection-to-string" function as follows.

SQL> CREATE FUNCTION to_string (
  2                  nt_in        IN varchar2_ntt,
  3                  delimiter_in IN VARCHAR2 DEFAULT ','
  4                  ) RETURN VARCHAR2 IS
  5
  6     v_idx PLS_INTEGER;
  7     v_str VARCHAR2(32767);
  8     v_dlm VARCHAR2(10);
  9
 10  BEGIN
 11
 12     v_idx := nt_in.FIRST;
 13     WHILE v_idx IS NOT NULL LOOP
 14        v_str := v_str || v_dlm || nt_in(v_idx);
 15        v_dlm := delimiter_in;
 16        v_idx := nt_in.NEXT(v_idx);
 17     END LOOP;
 18
 19     RETURN v_str;
 20
 21  END to_string;
 22  /

Function created.

Now we are ready to demonstrate string aggregation using the COLLECT function. We'll again collect the employee names per department, but this time we will display them in a comma-delimited string.

SQL> SELECT deptno
  2  ,      TO_STRING(CAST(COLLECT(ename) AS varchar2_ntt)) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

    DEPTNO EMPS
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

Now we can compare the performance of this method to the STRAGG implementation. We'll start by building a larger dataset to work with. We'll create a table with four sets of DBA_OBJECTS data.

SQL> CREATE TABLE t
  2  AS
  3     SELECT MOD(ROWNUM,100)          AS id
  4     ,      CAST('A' AS VARCHAR2(1)) AS val
  5     FROM   dba_objects
  6     ,      TABLE(varchar2_ntt('A','B','C','D'));

Table created.

SQL> SELECT COUNT(*) FROM t;

  COUNT(*)
----------
    193900
    
1 row selected.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'T');

PL/SQL procedure successfully completed.

Now we have almost 200K rows to work with, we'll aggregate the VAL column into a delimited string, using STRAGG. We'll use a TIMER package for wall-clock timings and autotrace for statistics.

SQL> set autotrace traceonly statistics

SQL> exec timer.snap();

PL/SQL procedure successfully completed.

SQL> SELECT id
  2  ,      STRAGG(val) AS vals
  3  FROM   t
  4  GROUP  BY
  5         id;

100 rows selected.

Statistics
----------------------------------------------------------
        221  recursive calls
          9  db block gets
        551  consistent gets
        395  physical reads
          0  redo size
       5213  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          3  sorts (memory)
          1  sorts (disk)
        100  rows processed

SQL> exec timer.show('STRAGG');

[STRAGG] 7.20 seconds

PL/SQL procedure successfully completed.

Now we have a rough timing for STRAGG, we can move onto the COLLECT function. The syntax is not quite as simple as STRAGG, as we've seen, but the time-savings are significant.

SQL> exec timer.snap();

PL/SQL procedure successfully completed.

SQL> SELECT id
  2  ,      TO_STRING(CAST(COLLECT(val) AS varchar2_ntt)) AS vals
  3  FROM   t
  4  GROUP  BY
  5         id;

100 rows selected.

Statistics
----------------------------------------------------------
       4441  recursive calls
        111  db block gets
       2519  consistent gets
        109  physical reads
      22040  redo size
       5213  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
        104  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> exec timer.show('COLLECT');

[COLLECT] 1.21 seconds

PL/SQL procedure successfully completed.

We can see that this is significantly faster. Yet we might also notice that many of the statistics are showing considerably more work being performed by Oracle in support of COLLECT. Most interestingly the number of recursive calls and sorts are much higher than for the STRAGG method. How can the COLLECT function be faster? The answer is not displayed by autotrace; it is context-switching. In the STRAGG implementation, there's a context-switch for every value being aggregated (in our example, roughly 193,000). Yet in the COLLECT example, we are only context-switching 100 times (once for every call to TO_STRING). As we know from the 8i days when BULK COLLECT was making headway, context-switching penalties can be high and we can see this once again.

Oracle ERP World

Pages

Wednesday, March 6, 2013

the collect function in 10g