Merge speed

Besides brevity - does MERGE actually give performance benefits over a traditional INSERT-UPDATE approach ?

SQL> create table T1 (
  2    x1 number primary key,
  3    y1 number );

Table created.

SQL> create table T2 (
  2    x2 number primary key,
  3    y2 number );

Table created.

SQL> insert into t1
  2  select rownum,rownum from all_objects
  3  where rownum <= 10000;

10000 rows created.

SQL> insert into t2
  2  select rownum,rownum+1 from all_objects
  3  where rownum <= 20000;

20000 rows created.

SQL> analyze table t1 estimate statistics;

Table analyzed.

SQL> analyze table t2 estimate statistics;

Table analyzed.

SQL> begin
  2    update t1
  3    set y1 = ( select y2 from t2 where x2 = t1.x1 )
  4    where x1 in ( select x2 from t2 );
  5
  6    insert into t1
  7    select * from t2
  8    where x2 not in ( select x1 from t1 ) ;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> roll;
Rollback complete.
SQL> begin
  2   MERGE INTO t1
  3   USING t2
  4   ON (t1.x1 = t2.x2)
  5   WHEN MATCHED THEN
  6     UPDATE SET t1.y1 = t2.y2
  7   WHEN NOT MATCHED THEN
  8     INSERT (x1,y1) VALUES (x2,y2) ;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Now we look at the trace file content

UPDATE T1 SET Y1 = ( SELECT Y2 FROM T2
WHERE
 X2 = T1.X1 ) WHERE X1 IN ( SELECT X2 FROM T2 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.00          0          0          0           0
Execute      3      3.81       5.46         49     100278      51214       50000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      3.82       5.46         49     100278      51214       50000

INSERT INTO T1 SELECT * FROM T2 WHERE X2 NOT IN ( SELECT X1 FROM T1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.00          0          0          0           0
Execute      3      0.59       0.94         45        342      19623       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.60       0.94         45        342      19623       10000

MERGE INTO T1 USING T2 ON (T1.X1 = T2.X2) WHEN MATCHED THEN UPDATE SET T1.Y1 =
   T2.Y2 WHEN NOT MATCHED THEN INSERT (X1,Y1) VALUES (X2,Y2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      2.73       6.24         23        310      70827       60000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      2.73       6.25         23        310      70827       60000


Looks like MERGE is our guy