MERGE verbrennt Sequenznummern

In einem vergangenen Blog-Beitrag haben wir die Verwendung des MERGE-Statemtents näher beleuchtet. Bei der Implementierung der Lösung fiel auf, das die zur Generierung der IDs verwendete Sequence wesentlich höhere IDs lieferte, als erwartet. Dem sind wir natürlich auf den Grund gegangen. 

Im konkreten Fall wurde die ID direkt per DEFAULT befüllt. 

SQL> create sequence tm_seq;

Sequence created.

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

Table created.

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='TM_SEQ';

LAST_NUMBER
-----------
          1

Werden nun Datensätze per MERGE hinzugefügt, so werden die IDs über die Sequence automatisch generiert.

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.

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='TM_SEQ';

LAST_NUMBER
-----------
     100001

Am Ende hat die Sequence wie erwartet um die Anzahl der hinzugefügten Datensätze zugenommen. Wenn wir dieses MERGE nun ein zweites Mal ausführen, so werden keine Datensätze mehr hinzugefügt sondern nur noch geändert.

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.

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='TM_SEQ';

LAST_NUMBER
-----------
     200001

Aber wie man sieht, hat sich die Sequence erneut um 100.000 erhöht. Die Werte werden als scheinbar auf Verdacht vorgeneriert obwohl diese am Ende gar nicht benötigt werden.

Selbst wenn wir die gleichen Zeilen mehrfach ändern und damit einen Fehler verursachen, werden diese IDs verbrannt:

SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(mod(level,10000)) zeile from dual connect by level < 500001
  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  ;
merge into test_merge
          *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='TM_SEQ';

LAST_NUMBER
-----------
     700021

 Im nächsten Versuch wurde die Zuweisung von DEFAULT auf IDENTITY geändert. Da aber auch dabei im Hintergrund eine Sequence verwendet wird, ist das Verhalten praktisch identisch:

SQL> create table test_merge (
  2   id  number generated by default on null as identity,
  3   txt varchar2(30),
  4   last_updated date,
  5   constraint pk_test_merge primary key (id)
  6  )
  7  /

Table created.

SQL> select sequence_name from user_tab_identity_cols where table_name='TEST_MERGE';

SEQUENCE_NAME
------------------------------------------------------------------------------------------------------------------
ISEQ$$_1193040

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='ISEQ$$_1193040';

LAST_NUMBER
-----------
          1

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.

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.

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='ISEQ$$_1193040';

LAST_NUMBER
-----------
     200001

Aber was passiert bei der Verwendung des ursprünglichen Ansatzes mit einem Trigger?

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

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='TM_SEQ';

LAST_NUMBER
-----------
     100001

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.

SQL> select LAST_NUMBER from user_sequences where SEQUENCE_NAME='TM_SEQ';

LAST_NUMBER
-----------
     100001

Hier ist der Effekt nicht zu sehen. Das ist auch logisch, denn der Trigger feuert erst, wenn tatsächlich eine Zeile per INSERT eingefügt wird, nicht aber bei einem UPDATE. Bei den ersten Ansätzen wird offenbar der DEFAULT schon generiert, wenn noch nicht klar ist, ob das INSERT überhaupt stattfinden wird. Allerdings ist dieser Effekt eher kosmetisch, denn die IDs sollten nur als eindeutige Werte dienen und nie irgend eine Relevanz für die Anwendung haben. Am Ende war die Variante mit Trigger sogar langsamer als die Varianten mit DEFAULT oder IDENTITY. Hier ist der Overhead des Triggers sogar messbar.