Merge vs. Insert-Exception-Update

Recently I had a discussion with one of our developer about how to bring new rows in a table or, if the row already exists, update it. His approach was a PL/SQL procedure that first tries to insert a single row and if that fails because the row is already there, catch that exception and do an update instead. I told him, that this approach has at least three issues, that make it slow. First, it does things row-by-row, this is much more inefficient than set operations and it does not scale well. Second, it has lots of context switches between SQL and PL/SQL which are costly. And third, the exception handling is expensive too. My advise was to switch the code to use a single MERGE-statement. And to prove that I am right, I wrote a small example which I’ll share with you.

Let’s create two tables with identical structure, both will have an unique index and a trigger, that fills the ID using a sequence.

SQL> create unique index tm_txt_uq on test_merge(txt);

Index created.

SQL> create table test_except (
  2   id  number,
  3   txt varchar2(30),
  4   last_updated date,
  5   constraint pk_test_except primary key (id)
  6  )
  7  /

Table created.

SQL> create unique index te_txt_uq on test_except(txt);

Index created.

SQL> create sequence tm_seq;

Sequence created.

SQL> create sequence te_seq;

Sequence created.

SQL> create or replace trigger tm_bir
  2  before insert on test_merge
  3  for each row
  4  begin
  5    :new.id := tm_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger te_bir
  2  before insert on test_except
  3  for each row
  4  begin
  5    :new.id := te_seq.nextval;
  6  end;
  7  /

Trigger created.

In the first run I populated both tables with 100k rows, since both tables are empty, this will lead to INSERTs of cause. And SQL*Plus will do the timing for me.

SQL> set timing on
SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(level) zeile from dual connect by level  set serveroutput on
SQL> declare
  2    l number;
  3    i number :=0;
  4    u number :=0;
  5  begin
  6    for l in 1..100000 loop
  7      begin
  8        insert into test_except (txt)
  9    values ('Zeile ' || to_char(l));
 10    i := i+1;
 11  exception
 12    when dup_val_on_index then
 13      update test_except set last_updated=sysdate where txt = 'Zeile ' || to_char(l);
 14      u := u+1;
 15      end;
 16    end loop;
 17    dbms_output.put_line(to_char(i) || ' rows inserted.');
 18    dbms_output.put_line(to_char(u) || ' rows updated.');
 19  end;
 20  /
100000 rows inserted.
0 rows updated.

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.62

We see, that the MERGE is more than 2x faster than the PL/SQL approach. When I now run both statements a scond time, it will lead to UPDATEs since all the rows are already in the tables.

SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(level) zeile from dual connect by level  declare
  2    l number;
  3    i number :=0;
  4    u number :=0;
  5  begin
  6    for l in 1..100000 loop
  7      begin
  8        insert into test_except (txt)
  9    values ('Zeile ' || to_char(l));
 10    i := i+1;
 11      exception
 12    when dup_val_on_index then
 13      update test_except set last_updated=sysdate where txt = 'Zeile ' || to_char(l);
 14      u := u+1;
 15      end;
 16    end loop;
 17    dbms_output.put_line(to_char(i) || ' rows inserted.');
 18    dbms_output.put_line(to_char(u) || ' rows updated.');
 19  end;
 20  /
0 rows inserted.
100000 rows updated.

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.36

The MERGE statement is even faster compared to the first run. That might be due to the fact, that there is less internal work to do, no index block splits etc. But the elapsed time of the PL/SQL aproach dicreased dramatically, it took 4x longer than it’s initial run and is now 11x slower then the MERGE.
So now it is really obvious, that the PL/SQL approach might not be the best performing solution. The MERGE solution maybe needs some more thinking and might not readable as the PL/SQL solution, but if done regularly one should get used to it.
The good thing is, I convinced my collegue.