MERGE – concise syntax but not always fastest – Learning is not a spectator sport

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>

3 comments

Yuri says:

March 29, 2013 at 9:13 pm

Is wallclock a good way to measure sql performance?

1. connormcdonald says:
  
  March 30, 2013 at 11:09 am
  
  In this case I would contend yes…. Its all PL/SQL and all running on the db server. If I was bringing rows back to the client, or something else like that which would impact the result, then a sql trace or similar might be preferred.
  
  Similarlly, for “comparison” taken to a more careful level, we could ensure there are no log switches, no autoextend, no other load on the machine that could possibly perturbate the results…
  
  Also similarly, for “performance” taken to a more careful level, we could check latching activity, redo consumption, etc etc…
  
  But the point of my blog is more rudimentary – namely, just because you *can* rewrite some existing code as a MERGE, doesn’t automatically mean that you should.
  
  Thanks for stopping by Yuri
  
  Cheers
  Connor
  
Pingback: MERGE vs UPDATE/INSERT revisited | Learning is not a spectator sport