upsert VS merge

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6618304976523

ops$[e-mail]> 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$[e-mail]> alter table ao  2  add constraint  3  ao_pk primary key(object_id);Table altered.ops$[e-mail]> insert into ao select * from all_objects;29311 rows created.ops$[e-mail]> create table t1  2  as  3  select *  4    from all_objects  5    where rownum<= (select count(*)/2 from all_objects);Table created.ops$[e-mail]> alter table t1 add constraint t1_pk primary key(object_id);Table altered.ops$[e-mail]> analyze table t1 compute statistics  2  for table for all indexes for all indexed columns;Table analyzed.ops$[e-mail]> create table t2  2  as  3  select *  4    from all_objects  5   where rownum<= (select count(*)/2 from all_objects);Table created.ops$[e-mail]> alter table t2 add constraint t2_pk primary key(object_id);Table altered.ops$[e-mail]> 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$[e-mail]> 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 hsecs2116 hsecsrun 1 ran in 20.04% of the timePL/SQL procedure successfully completed.merge is faster wallclock wise then procedural code and...ops$[e-mail]> 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      15009LATCH.redo allocation               30780      46012      15232STAT...db block gets                47239      62630      15391STAT...table scan blocks gotte        597      29311      28714nSTAT...buffer is not pinned co        693      29409      28716untSTAT...index fetch by key               9      29320      29311STAT...db block changes             60912      90825      29913STAT...no work - consistent re        260      36398      36138ad getsSTAT...calls to get snapshot s        450      44200      43750cn: kcmgssSTAT...execute count                   63      44015      43952LATCH.shared pool                     463      44606      44143STAT...consistent gets - exami        729      51860      51131nationSTAT...recursive calls                838      73844      73006STAT...consistent gets               1748      88444      86696LATCH.library cache pin               436      88558      88122LATCH.library cache                   757      89093      88336STAT...session pga memory           95732          0     -95732STAT...session logical reads        48987     151074     102087LATCH.cache buffers chains         212197     405774     193577STAT...session pga memory max      947700          0    -947700STAT...redo size                 12908776   16933156    4024380100 rows selected.ops$[e-mail]>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$[e-mail]> 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$[e-mail]> create type myArrayType as table of myScalarType  2  /Type created.ops$[e-mail]>ops$[e-mail]> 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$[e-mail]> 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 hsecs1737 hsecsrun 1 ran in 28.44% of the timePL/SQL procedure successfully completed. ops$[e-mail]> 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     128224STAT...session uga memory               0     130928     130928STAT...session uga memory max           0     130928     130928LATCH.row cache enqueue latch         362     177614     177252LATCH.row cache objects               448     184995     184547LATCH.cache buffers chains         211442     493338     281896STAT...session pga memory max      956224     166944    -789280STAT...redo size                 12876460   14459964    1583504106 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.