22. Oktober 2009

Neu in Oracle 11.2 und praktisch: Der IGNORE_ROW_ON_DUPKEY_INDEX Hint

English title: Oracle 11.2: The IGNORE_ROW_ON_DUPKEY_INDEX Hint

Heute will ich mich mal dem Thema Oracle11g Release 2 widmen. Im Rahmen der deutschen APEX-Community gab es ja bereits Anfang September einen ersten Einblick in die neuen Features. Dieses Blog-Posting widmet sich einem Feature, das nicht auf der Liste ist: Den neuen Optimizer-Hints zum Handling von Duplicate Key Violations bei SQL INSERT-Kommandos. Die Ausgangssituation:
Today I'll start writing about the new database release 11.2. For german readers the APEX community website contains some information about new features since 11.2 was released in september. This blog posting is about a feature which is not on that list: There are new optimizer hints which can help handling duplicate key violations on SQL insert commands. An Example:
create table test_tab (
  id   number(10) primary key,
  name varchar2(20)
)
/

insert into test_tab values (1, 'Oracle')
/

commit
/
Möchte man nun nochmals eine Zeile mit der ID 1 einfügen, so gibt es eine Fehlermeldung - das ist bekannt. Wenn man nun mehrere Zeilen auf einmal einfügen möchte (und man nicht weiss, ob diese teilweise Unique Key Violations auslösen würden), so bieten sich bislang zwei Varianten an:
  • Man baut eine PL/SQL-Schleife, geht darin durch die einzufügenden Zeilen durch (SELECT-Loop), probiert mit jeder einzelnen den INSERT und fängt etwaige Exceptions ab. Das muss allerdings codiert werden, ist langsam und fehleranfällig
  • Man nutzt das MERGE-Kommando
If you now try to insert another row with the ID value of 1 you get an error message - this is clear. If you now want to insert multiple rows into that table (and you don't know whether there will be unique key violations) there are two approaches:
  • Use PL/SQL to build a loop on the SELECTed rows, try an INSERT for each row and catch the exception. This has to be coded, is errornous and slow.
  • A better approach is the MERGE command
In Oracle 11.2 gibt es eine dritte Variante per Optimizer-Hint (IGNORE_ROW_ON_DUPKEY_INDEX): Und das geht so:
Oracle 11.2 now has a third variant: The optimizer hint IGNORE_ROW_ON_DUPKEY_INDEX.
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(test_tab(id))*/ into test_tab values (1, 'MySQL')
/

0 rows inserted.
Wie man sieht, wird zwar nichts eingefügt, es wird aber auch keine Fehlermeldung ausgelöst. Der Hint funktioniert nur bei Single Table-INSERT-Kommandos. Laut Dokumentation ist dies trotz Hint-Syntax ein sog. Mandate, wird vom Optimizer also auf jeden Fall umgesetzt. Normalerweise ist ein Hint - wie der Name schon sagt - ein Hinweis an den Optimizer, den dieser auch ignorieren darf. Ich finde das eine ganz nette Variante, da es mir die PL/SQL-Loops erspart und doch ein wenig einfacher als MERGE ist. Mehr Infos zu 11.2 in den nächsten Blog-Postings - Stay tuned ...
As you can see: There is no row being inserted but also no error message - the statement succeeds. This hint does only work for Single Table INSERT statements. The documentation states that this hint is indeed a mandate - so for the optimizer it is a mandatory command. Unlike a classic hint it cannot choose between following or not following it. IMHO this is a nice and easy alternative to a MERGE command - PL/SQL loops are still not necessary and it's much easier to use. I'll do more blog postings about 11.2 features in the near future - Stay tuned ...

Keine Kommentare:

Beliebte Postings