MERGE – concise syntax but not always fastest

A long time ago … a long long time ago (http://www.oracledba.co.uk/tips/merge_speed.htm) I wrote that MERGE works best over update and insert.

This still holds true, but its also apparent that MERGE seems to be optimized for larger sets of rows.  When you take it down to single row operations, then don’t be SO hasty to recast your updates-and-inserts into merge commands.

SQL> create table t1
  2   ( x int primary key,
  3     y int );

Table created.

SQL>
SQL> -- all inserts
SQL>
SQL> set timing on
SQL> begin
  2  for i in 1 .. 50000 loop
  3    merge into t1
  4    using ( select i x, i y
  5            from dual ) m
  6    on ( t1.x = m.x )
  7    when matched then update
  8    set t1.y = m.y
  9    when not matched then
 10    insert values (m.x, m.y );
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.79
SQL>
SQL> -- 50/50
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    merge into t1
  4    using ( select i x, i y
  5            from dual ) m
  6    on ( t1.x = m.x )
  7    when matched then update
  8    set t1.y = m.y
  9    when not matched then
 10    insert values (m.x, m.y );
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.31
SQL>
SQL> -- all updates
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    merge into t1
  4    using ( select i x, i+1 y
  5            from dual ) m
  6    on ( t1.x = m.x )
  7    when matched then update
  8    set t1.y = m.y
  9    when not matched then
 10    insert values (m.x, m.y );
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.14
SQL>
SQL>
SQL> drop table t1 purge;

Table dropped.

Elapsed: 00:00:00.35
SQL>
SQL> create table t1
  2   ( x int primary key,
  3     y int );

Table created.

Elapsed: 00:00:00.03
SQL>
SQL> -- all inserts
SQL> set timing on
SQL> begin
  2  for i in 1 .. 50000 loop
  3    update t1 set y = i where x = i;
  4    if sql%notfound then insert into t1 values (i,i); end if;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.65
SQL>
SQL> -- 50/50
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    update t1 set y = i where x = i;
  4    if sql%notfound then insert into t1 values (i,i); end if;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.20
SQL>
SQL> -- all updates
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    update t1 set y = i+1 where x = i;
  4    if sql%notfound then insert into t1 values (i,i+1); end if;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.94
SQL>