Datensätze erstellen oder ändern - MERGE vs EXCEPTION

Hin und wieder kommt es vor, das z.B. beim Laden von Daten nicht klar ist, ob ein Datensatz bereits existiert und er geändert werden muss oder ob der Datensatz neu hinzuzufügen ist. Ein oft gesehener Ansatz basiert auf dem Prinzip Versuch und Fehler. Es wird versucht, den Datensatz per INSERT neu einzufügen, schlägt das wegen einer Verletzung des Primärschlüssels fehl, so wird in der Fehlerbehandlung ein UPDATE auf den Datensatz durchgeführt. Aber ist dieses Vorgehen überhaupt effizient? 

Um diese Frage zu beantworten, haben wir in einem kleinen Testfall die Performance eines MERGE-Statements mit der des Exception-Handlers verglichen. Das MERGE-Statement erlaubt es innerhalb einer Operation, einen Datensatz entweder zu ändern oder neu hinzuzufügen, je nachdem was erforderlich ist. 

Für den Testfall erzeugen wir zwei strukturgleiche Tabellen, die jeweils einen Primärschlüssel besitzen. Dieser Primärschlüssel wird jeweils über einen Trigger aus einer Sequence gefüllt.

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

Table created.

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.

Zuerst befüllen wir die Tabellen per INSERT, denn initial sind noch keine Daten vorhanden. Zur Zeitmessung bemühen wir das SQL*Plus selbst.

SQL> set timing on
SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(level) zeile from dual connect by level < 100001
  4  ) quelle
  5  on (quelle.zeile = test_merge.txt)
  6  when not matched then
  7    insert (test_merge.txt, test_merge.last_updated)
  8    values (quelle.zeile, sysdate)
  9  when matched then
 10    update set test_merge.last_updated = sysdate
 11  ;

100000 rows merged.

Elapsed: 00:00:06.43

SQL> 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

Man sieht, das das MERGE Statement mehr als doppelt so schnell läuft im Vergleich zum PL/SQL Ansatz.

Wenn das Ganze nun ein zweites mal läuft, dann müssen die Datensätze geändert werden. Schauen wir uns die Laufzeiten des zweiten Durchgangs an.

SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(level) zeile from dual connect by level < 100001
  4  ) quelle
  5  on (quelle.zeile = test_merge.txt)
  6  when not matched then
  7    insert (test_merge.txt, test_merge.last_updated)
  8    values (quelle.zeile, sysdate)
  9  when matched then
 10    update set test_merge.last_updated = sysdate
 11  ;

100000 rows merged.

Elapsed: 00:00:05.48

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  /
0 rows inserted.
100000 rows updated.

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.36

Die Laufzeit des MERGE ist eher noch gesunken im Vergleich zum ersten Durchlauf. Das liegt vermutlich daran, dass keine neuen Blöcke, weder im Index noch in der Tabelle, erzeugt werden müssen. Die Laufzeit des PL/SQL hat sich hingegen um Faktor 4 verlangsamt im Vergleich zum ersten Lauf. 

Es liegt also klar auf der Hand, dass das Exception Handling im PL/SQL einen enormen Overhead bedeutet. Die vielen Kontext Switche von SQL zu PL/SQL und zurück verschlechtern die Laufzeit der Prozedur ebenfalls im Vergleich zum MERGE. Außerdem arbeitet das MERGE-Statement mengenorientiert und nicht zeilenweise wie es das PL/SQL tut. Auch das ist ein maßgeblicher Faktor für die Performance. Obwohl das PL/SQL vielleicht auf den ersten Blick etwas lesbarer und einleuchtender erscheint, sollte man immer die Alternativen im Blick behalten. Ein kurzer Test wie in diesem Beispiel bringt oftmals erstaunliche Ergebnisse zu Tage und verhindert spätere Performance-Probleme, wenn es an den Echtbetrieb geht.