upsert VS merge

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.