Collections can be a great help in speeding up the PL/SQL programs. By using bulk collect operations it is possible to get great performance improvements.
In 9.2 we needed to use the bulk collect clause to fetch rows into a collection. 10G brings a new function called COLLECT, which takes a column as a parameter and returns a nested table containing the column values. Using this we can get the data into a collection without using bulk collect.
Here is a very simple demo of this new function.
YAS@10G>create table t as select * from all_objects;
Table created.
YAS@10G>create or replace type name_type as table of varchar2(30);
2 /
Type created.
YAS@10G>set serveroutput on
YAS@10G>r
1 declare
2 v_names name_type;
3 begin
4 select cast(collect(object_name) as name_type) into v_names from t;
5 dbms_output.put_line(v_names.count);
6 dbms_output.put_line(v_names(1));
7* end;
42268
ICOL$
PL/SQL procedure successfully completed.
One difference between this and bulk collect is, since this a sql function we need a sql type for this, it cannot be used with local PL/SQL types.
YAS@10G>r
1 declare
2 type name_type is table of varchar2(30);
3 v_names name_type;
4 begin
5 select collect(object_name) into v_names from t;
6* end;
select collect(object_name) into v_names from t;
*
ERROR at line 5:
ORA-06550: line 5, column 35:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 5, column 9:
PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got -
ORA-06550: line 5, column 2:
PL/SQL: SQL Statement ignored
Another difference, and a more important one, is the performance difference between these two constructs. Using Tom Kyte's runstats package, I did a test to compare the two. I ran the test several times, the results were similar.
YAS@10G>r
1 declare
2 v_names name_type;
3 begin
4 runStats_pkg.rs_start;
5
6 for i in 1..1000 loop
7 select object_name bulk collect into v_names from t;
8 end loop;
9
10 runStats_pkg.rs_middle;
11
12 for i in 1..1000 loop
13 select cast(collect(object_name) as name_type) into v_names from t;
14 end loop;
15
16 runStats_pkg.rs_stop;
17
18* end;
Run1 ran in 1329 hsecs
Run2 ran in 4060 hsecs
run 1 ran in 32.73% of the time
Name Run1 Run2 Diff
LATCH.qmn state object latch 0 1 1
STAT...redo entries 9 10 1
LATCH.JS slv state obj latch 1 0 -1
LATCH.qmn task queue latch 5 6 1
LATCH.transaction branch alloc 0 1 1
LATCH.sort extent pool 0 1 1
LATCH.resmgr:actses change gro 1 0 -1
LATCH.ncodef allocation latch 0 1 1
LATCH.slave class 0 1 1
LATCH.archive control 0 1 1
LATCH.FAL subheap alocation 0 1 1
LATCH.FAL request queue 0 1 1
STAT...heap block compress 6 5 -1
LATCH.session switching 0 1 1
LATCH.ksuosstats global area 1 2 1
LATCH.event group latch 1 0 -1
LATCH.threshold alerts latch 0 1 1
LATCH.list of block allocation 2 0 -2
LATCH.transaction allocation 2 0 -2
LATCH.dummy allocation 3 1 -2
LATCH.user lock 2 0 -2
LATCH.Consistent RBA 4 2 -2
STAT...calls to kcmgcs 4 6 2
STAT...active txn count during 4 6 2
STAT...cleanout - number of kt 4 6 2
STAT...consistent gets 587,009 587,011 2
STAT...consistent gets from ca 587,009 587,011 2
STAT...consistent gets - exami 4 6 2
LATCH.resmgr:free threads list 3 0 -3
LATCH.PL/SQL warning settings 3 0 -3
LATCH.OS process 6 3 -3
LATCH.compile environment latc 3 0 -3
LATCH.slave class create 0 3 3
LATCH.resmgr:actses active lis 3 0 -3
LATCH.cache buffers lru chain 0 3 3
LATCH.OS process allocation 10 14 4
LATCH.session state list latch 4 0 -4
LATCH.redo allocation 50 46 -4
STAT...consistent changes 17 22 5
LATCH.resmgr group change latc 5 0 -5
STAT...db block gets 17 22 5
STAT...db block gets from cach 17 22 5
LATCH.library cache pin alloca 6 0 -6
STAT...db block changes 26 32 6
STAT...session logical reads 587,026 587,033 7
LATCH.In memory undo latch 23 16 -7
LATCH.session idle bit 10 3 -7
LATCH.mostly latch-free SCN 6 14 8
LATCH.KMG MMAN ready and start 5 13 8
LATCH.lgwr LWN SCN 6 14 8
LATCH.simulator hash latch 34,010 34,001 -9
LATCH.simulator lru latch 34,010 34,001 -9
LATCH.session timer 5 14 9
LATCH.dml lock allocation 10 1 -9
LATCH.undo global data 20 10 -10
LATCH.post/wait queue 10 0 -10
LATCH.active checkpoint queue 4 15 11
LATCH.session allocation 13 2 -11
LATCH.archive process latch 4 15 11
LATCH.library cache lock alloc 16 0 -16
LATCH.object queue header oper 8 32 24
LATCH.client/application info 25 0 -25
LATCH.redo writing 24 51 27
LATCH.active service list 37 81 44
STAT...undo change vector size 2,124 2,200 76
LATCH.channel operations paren 60 197 137
LATCH.cache buffers chains 1,174,359 1,174,189 -170
LATCH.JS queue state obj latch 108 288 180
LATCH.messages 108 291 183
STAT...redo size 2,772 2,964 192
LATCH.checkpoint queue latch 80 282 202
LATCH.enqueue hash chains 269 652 383
LATCH.enqueues 248 645 397
LATCH.SQL memory manager worka 276 944 668
LATCH.shared pool 42 1,029 987
LATCH.library cache lock 170 2,018 1,848
LATCH.library cache pin 2,130 4,064 1,934
STAT...Elapsed Time 1,330 4,061 2,731
STAT...CPU used by this sessio 1,334 4,083 2,749
STAT...recursive cpu usage 1,268 4,064 2,796
LATCH.library cache 2,264 5,074 2,810
LATCH.row cache objects 49 9,015 8,966
STAT...session pga memory 2,097,152 1,441,792 -655,360
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,248,536 1,267,073 18,537 98.54%
PL/SQL procedure successfully completed.
As you see the bulk collect method completes in 1/3rd of the time of the collect function method. Using the collect function hits the library cache harder and uses more latch operations.
The COLLECT function can be used in sql statements to compare collections of columns. In Laurent Schneider's comment on my previous post you can find an example of it.
