http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6618304976523
ops$tkyte@ORA920.US.ORACLE.COM> create global temporary table ao 2 on commit PRESERVE ROWS 3 as 4 select * 5 from all_objects 6 where 1=0; Table created. ops$tkyte@ORA920.US.ORACLE.COM> alter table ao 2 add constraint 3 ao_pk primary key(object_id); Table altered. ops$tkyte@ORA920.US.ORACLE.COM> insert into ao select * from all_objects; 29311 rows created. ops$tkyte@ORA920.US.ORACLE.COM> create table t1 2 as 3 select * 4 from all_objects 5 where rownum<= (select count(*)/2 from all_objects); Table created. ops$tkyte@ORA920.US.ORACLE.COM> alter table t1 add constraint t1_pk primary key(object_id); Table altered. ops$tkyte@ORA920.US.ORACLE.COM> analyze table t1 compute statistics 2 for table for all indexes for all indexed columns; Table analyzed. ops$tkyte@ORA920.US.ORACLE.COM> create table t2 2 as 3 select * 4 from all_objects 5 where rownum<= (select count(*)/2 from all_objects); Table created. ops$tkyte@ORA920.US.ORACLE.COM> alter table t2 add constraint t2_pk primary key(object_id); Table altered. ops$tkyte@ORA920.US.ORACLE.COM> analyze table t2 compute statistics 2 for table for all indexes for all indexed columns; Table analyzed. So, t1 and t2 are for all intents and purposes the same -- we'll upsert them from AO.. ops$tkyte@ORA920.US.ORACLE.COM> declare 2 l_start number; 3 l_run1 number; 4 l_run2 number; 5 6 type rc is ref cursor; 7 l_cur rc; 8 begin 9 insert into run_stats select 'before', stats.* from stats; 10 11 l_start := dbms_utility.get_time; 12 merge into t1 13 using ao on ( t1.object_id = ao.object_id ) 14 when matched then 15 update set owner = ao.owner, 16 object_name = ao.object_name, 17 subobject_name = ao.subobject_name, 18 data_object_id = ao.data_object_id, 19 object_type = ao.object_type, 20 created = ao.created, 21 last_ddl_time = ao.last_ddl_time, 22 timestamp = ao.timestamp, 23 status = ao.status, temporary = ao.temporary, 24 generated = ao.generated, 25 secondary = ao.secondary 26 when not matched then 27 insert ( OWNER, OBJECT_NAME, 28 SUBOBJECT_NAME, OBJECT_ID, 29 DATA_OBJECT_ID, OBJECT_TYPE, 30 CREATED, LAST_DDL_TIME, 31 TIMESTAMP, STATUS, TEMPORARY, 32 GENERATED, SECONDARY ) 33 values ( ao.OWNER, ao.OBJECT_NAME, 34 ao.SUBOBJECT_NAME, ao.OBJECT_ID, 35 ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 36 ao.CREATED, ao.LAST_DDL_TIME, 37 ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 38 ao.GENERATED, ao.SECONDARY); 39 commit; 40 l_run1 := (dbms_utility.get_time-l_start); 41 dbms_output.put_line( l_run1 || ' hsecs' ); 42 43 insert into run_stats select 'after 1', stats.* from stats; 44 l_start := dbms_utility.get_time; 45 for x in ( select * from ao ) 46 loop 47 update t2 set ROW = x where object_id = x.object_id; 48 if ( sql%rowcount = 0 ) 49 then 50 insert into t2 values X; 51 end if; 52 end loop; 53 commit; 54 l_run2 := (dbms_utility.get_time-l_start); 55 dbms_output.put_line( l_run2 || ' hsecs' ); 56 dbms_output.put_line 57 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' ); 58 59 insert into run_stats select 'after 2', stats.* from stats; 60 end; 61 / 424 hsecs 2116 hsecs run 1 ran in 20.04% of the time PL/SQL procedure successfully completed. merge is faster wallclock wise then procedural code and... ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2, 2 ( (c.value-b.value)-(b.value-a.value)) diff 3 from run_stats a, run_stats b, run_stats c 4 where a.name = b.name 5 and b.name = c.name 6 and a.runid = 'before' 7 and b.runid = 'after 1' 8 and c.runid = 'after 2' 9 and (c.value-a.value) > 0 10 and (c.value-b.value) <> (b.value-a.value) 11 order by abs( (c.value-b.value)-(b.value-a.value)) 12 / NAME RUN1 RUN2 DIFF ------------------------------ ---------- ---------- ---------- ... STAT...redo entries 30661 45670 15009 LATCH.redo allocation 30780 46012 15232 STAT...db block gets 47239 62630 15391 STAT...table scan blocks gotte 597 29311 28714 n STAT...buffer is not pinned co 693 29409 28716 unt STAT...index fetch by key 9 29320 29311 STAT...db block changes 60912 90825 29913 STAT...no work - consistent re 260 36398 36138 ad gets STAT...calls to get snapshot s 450 44200 43750 cn: kcmgss STAT...execute count 63 44015 43952 LATCH.shared pool 463 44606 44143 STAT...consistent gets - exami 729 51860 51131 nation STAT...recursive calls 838 73844 73006 STAT...consistent gets 1748 88444 86696 LATCH.library cache pin 436 88558 88122 LATCH.library cache 757 89093 88336 STAT...session pga memory 95732 0 -95732 STAT...session logical reads 48987 151074 102087 LATCH.cache buffers chains 212197 405774 193577 STAT...session pga memory max 947700 0 -947700 STAT...redo size 12908776 16933156 4024380 100 rows selected. ops$tkyte@ORA920.US.ORACLE.COM> it did less work -- generated 75% of the redo and so on... Now, I ran that as a pipelined function (two merges -- merge vs merge) and the results were that the merge using a TABLE bested the pipelined function with about the same ratios. ... ops$tkyte@ORA920.US.ORACLE.COM> create type myScalarType as object ( 2 OWNER VARCHAR2(30), 3 OBJECT_NAME VARCHAR2(30), 4 SUBOBJECT_NAME VARCHAR2(30), 5 OBJECT_ID NUMBER, 6 DATA_OBJECT_ID NUMBER, 7 OBJECT_TYPE VARCHAR2(18), 8 CREATED DATE, 9 LAST_DDL_TIME DATE, 10 TIMESTAMP VARCHAR2(19), 11 STATUS VARCHAR2(7), 12 TEMPORARY VARCHAR2(1), 13 GENERATED VARCHAR2(1), 14 SECONDARY VARCHAR2(1) 15 ) 16 / Type created. ops$tkyte@ORA920.US.ORACLE.COM> create type myArrayType as table of myScalarType 2 / Type created. ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> create or replace function ao_function return myArrayType 2 PIPELINED 3 as 4 begin 5 for ao in (select * from all_objects) 6 loop 7 pipe row( myScalarType( ao.OWNER, ao.OBJECT_NAME, 8 ao.SUBOBJECT_NAME, ao.OBJECT_ID, 9 ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 10 ao.CREATED, ao.LAST_DDL_TIME, 11 ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 12 ao.GENERATED, ao.SECONDARY) ); 13 end loop; 14 return; 15 end; 16 / Function created. .... ops$tkyte@ORA920.US.ORACLE.COM> declare 2 l_start number; 3 l_run1 number; 4 l_run2 number; 5 6 type rc is ref cursor; 7 l_cur rc; 8 begin 9 insert into run_stats select 'before', stats.* from stats; 10 11 l_start := dbms_utility.get_time; 12 merge into t1 13 using ao on ( t1.object_id = ao.object_id ) 14 when matched then 15 update set owner = ao.owner, 16 object_name = ao.object_name, 17 subobject_name = ao.subobject_name, 18 data_object_id = ao.data_object_id, 19 object_type = ao.object_type, 20 created = ao.created, 21 last_ddl_time = ao.last_ddl_time, 22 timestamp = ao.timestamp, 23 status = ao.status, temporary = ao.temporary, 24 generated = ao.generated, 25 secondary = ao.secondary 26 when not matched then 27 insert ( OWNER, OBJECT_NAME, 28 SUBOBJECT_NAME, OBJECT_ID, 29 DATA_OBJECT_ID, OBJECT_TYPE, 30 CREATED, LAST_DDL_TIME, 31 TIMESTAMP, STATUS, TEMPORARY, 32 GENERATED, SECONDARY ) 33 values ( ao.OWNER, ao.OBJECT_NAME, 34 ao.SUBOBJECT_NAME, ao.OBJECT_ID, 35 ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 36 ao.CREATED, ao.LAST_DDL_TIME, 37 ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 38 ao.GENERATED, ao.SECONDARY); 39 commit; 40 l_run1 := (dbms_utility.get_time-l_start); 41 dbms_output.put_line( l_run1 || ' hsecs' ); 42 43 insert into run_stats select 'after 1', stats.* from stats; 44 l_start := dbms_utility.get_time; 45 merge into t2 46 using (select * from TABLE(ao_function)) ao on ( t2.object_id = ao.object_id ) 47 when matched then 48 update set owner = ao.owner, 49 object_name = ao.object_name, 50 subobject_name = ao.subobject_name, 51 data_object_id = ao.data_object_id, 52 object_type = ao.object_type, 53 created = ao.created, 54 last_ddl_time = ao.last_ddl_time, 55 timestamp = ao.timestamp, 56 status = ao.status, temporary = ao.temporary, 57 generated = ao.generated, 58 secondary = ao.secondary 59 when not matched then 60 insert ( OWNER, OBJECT_NAME, 61 SUBOBJECT_NAME, OBJECT_ID, 62 DATA_OBJECT_ID, OBJECT_TYPE, 63 CREATED, LAST_DDL_TIME, 64 TIMESTAMP, STATUS, TEMPORARY, 65 GENERATED, SECONDARY ) 66 values ( ao.OWNER, ao.OBJECT_NAME, 67 ao.SUBOBJECT_NAME, ao.OBJECT_ID, 68 ao.DATA_OBJECT_ID, ao.OBJECT_TYPE, 69 ao.CREATED, ao.LAST_DDL_TIME, 70 ao.TIMESTAMP, ao.STATUS, ao.TEMPORARY, 71 ao.GENERATED, ao.SECONDARY); 72 commit; 73 l_run2 := (dbms_utility.get_time-l_start); 74 dbms_output.put_line( l_run2 || ' hsecs' ); 75 dbms_output.put_line 76 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' ); 77 78 insert into run_stats select 'after 2', stats.* from stats; 79 end; 80 / 494 hsecs 1737 hsecs run 1 ran in 28.44% of the time PL/SQL procedure successfully completed. ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2, 2 ( (c.value-b.value)-(b.value-a.value)) diff 3 from run_stats a, run_stats b, run_stats c 4 where a.name = b.name 5 and b.name = c.name 6 and a.runid = 'before' 7 and b.runid = 'after 1' 8 and c.runid = 'after 2' 9 and (c.value-a.value) > 0 10 and (c.value-b.value) <> (b.value-a.value) 11 order by abs( (c.value-b.value)-(b.value-a.value)) 12 / .... STAT...session pga memory 104256 232480 128224 STAT...session uga memory 0 130928 130928 STAT...session uga memory max 0 130928 130928 LATCH.row cache enqueue latch 362 177614 177252 LATCH.row cache objects 448 184995 184547 LATCH.cache buffers chains 211442 493338 281896 STAT...session pga memory max 956224 166944 -789280 STAT...redo size 12876460 14459964 1583504 106 rows selected. It is the removal of the procedural, handwritten code here that makes the difference. We should strive for ways to do things SET ORIENTED (as sql likes that best). The less procedural code we write in general the better.