4. März 2009

INSERT, UPDATE oder DELETE in einem: SQL MERGE

English title: INSERT, UPDATE or DELETE in a single statement: MERGE

Wenn man Datensätze in eine Tabelle "mischen" möchte (bspw. Kundenänderungen in eine Bestandstabelle einpflegen), muss man INSERT oder UPDATE-Anweisungen auslösen - je nachdem, ob der Kunde schon da ist oder nicht. Vielfach hilft man sich dann mit PL/SQL-Prozeduren, die zuerst mal einen INSERT versuchen, und wenn ein Fehler auftritt (DUP_VAL_ON_INDEX) ein UPDATE machen.
If one wants to "merge" rows into a table (e.g. to process a set of customer data changes) either SQL INSERT or SQL UPDATE commands have to be issued. If the customer is already there the operation is a SQL UPDATE to reflect the changes - if the customer is new an INSERT must be issued in order to create the table row. In most cases PL/SQL logic is being written; first try an INSERT and if this fails (DUP_VAL_ON_INDEX) do the UPDATE.
Das geht aber auch eleganter - mit dem MERGE-Kommando. Dieses gibt es bereits seit Oracle9i - in Oracle10g wurde es nochmals erweitert. Recht nützlich ist das Kommando im DWH-Umfeld - im Rahmen der Ladeprozesse ist das Einpflegen von Änderungsdaten normal ...
Since Oracle9i there is the SQL MERGE command. MERGE is INSERT and UPDATE (and as we'll see later) DELETE in one SQL statement. So no procedural logic is needed any more. MERGE is very useful in loading operations for datawarehouses - here its quite common that the actual data set is just delta information and has to be merged into the final (and very large) warehouse tables.
Hier ist ein einfaches Beispiel mit der EMP-Tabelle. Die Tabelle EMP_NEW enthält bereits einige Zeilen, die Inhalte aus der Tabelle EMP sollen dort hineingemischt werden.
Here is a simple example of SQL MERGE on the well-known EMP table. The EMP_NEW table already contains some rows of the EMP table and we now want to merge the other rows.
merge into emp_new dest
using (select * from emp) src
on (src.empno = dest.empno)
when matched then update set 
  dest.ename    = src.ename,
  dest.sal      = src.sal,
  dest.hiredate = src.hiredate,
  dest.job      = src.job,
  dest.deptno   = src.deptno,
  dest.comm     = src.comm,
  dest.mgr      = src.mgr
when not matched then 
  insert (empno, ename, sal, comm, mgr, job, hiredate, deptno)
  values (src.empno, src.ename, src.sal, src.comm, src.mgr, src.job, src.hiredate, src.deptno)
/
Man kann sehr schön die Abschnitte erkennen:
  • MERGE INTO ... gibt das Ziel der MERGE-Operation an (typischerweise eine Tabelle)
  • USING ... gibt die Datenquelle an; das kann eine Tabelle, ein View oder (wie hier) eine SELECT-Abfrage sein.
  • ON (matching-kriterium) legt fest, wie bereits vorhandene Zeilen erkannt werden sollen - hier anhand der EMPNO. Auch komplerere Kriterien sind möglich.
  • WHEN MATCHED ... legt fest, was passieren soll, wenn die Zeile schon da ist; in diesem Fall soll ein UPATE erfolgen; die Spalteninhalte der Zieltabelle sollen mit denen der Quelldaten überschrieben werden.
  • WHEN NOT MATCHED ... legt fest, was passieren soll, wenn die Zeile nicht vorhanden ist; in diesem Fall wird sie anhand der Quelldaten erzeugt.
The MERGE statement structure is easy to understand:
  • MERGE INTO ... defines the target of the merge operation (a table)
  • USING ... defines the data souurce - this might be a table, a view or (as here) a SQL query.
  • ON (matching-condition) tells the database how to determine the existing row. In this casewe use the EMPNO column for that. Multiple columns are also possible.
  • WHEN MATCHED ... specify here what to do if the row already exists. Typically the UPDATE operation is performed in this case.
  • WHEN NOT MATCHED ... specify here what to do if the row is not present. Typically the INSERT operation is performed in this case.
MERGE kann jedoch ab Oracle10g noch mehr ... So sollen die DEPTNO 30 verschwinden; alle Zeilen der DEPTNO 30 sollen also nicht mehr in die Zieltabelle gemischt werden.
But MERGE can even do more (Oracle10g or higher). Let's assume that the rows of department 30 should not be merged.
merge into emp_new dest
using (select * from emp) src
on (src.empno = dest.empno)
when matched then update set 
  dest.ename    = src.ename,
  dest.sal      = src.sal,
  dest.hiredate = src.hiredate,
  dest.job      = src.job,
  dest.deptno   = src.deptno,
  dest.comm     = src.comm,
  dest.mgr      = src.mgr
  -- ONLY IF DEPTNO IN SOURCE DATA NOT EQUALS "30"
  where not src.deptno = 30
when not matched then 
  insert (empno, ename, sal, comm, mgr, job, hiredate, deptno)
  values (src.empno, src.ename, src.sal, src.comm, src.mgr, src.job, src.hiredate, src.deptno)
  -- ONLY IF DEPTNO IN SOURCE DATA NOT EQUALS "30"
  where not src.deptno = 30
/
Mal ganz was Neues: ein SQL INSERT mit WHERE-Bedingung - nach diesem MERGE sieht die Tabelle EMP_NEW etwas anders aus - alle Zeilen mit DEPTNO = 30 wurden nicht übertragen. Aber die Zeilen, die bereits vor der MERGE-Operation existierten, sind immer noch da. Der nächste Schritt wäre es nun, die Zeile in der Zieltabelle zu löschen, wenn in der Quelltabelle die DEPTNO auf "30" gesetzt ist. Und MERGE kann auch das (ab Oracle10g): Mit der DELETE-Klausel:
This is new: A SQL INSERT with a WHERE condition - after this MERGE operation the EMP_NEW table looks different. All rows with DEPTNO = 30 were not merged. But the rows which already existed before the merge operation are still there. So the next step is to delete the rows in the target table of the DEPTNO column in the source data equals "30". And MERGE can also do this (Oracle10g or higher): Using the DELETE clause:
merge into emp_new dest
using (select * from emp) src
on (src.empno = dest.empno)
when matched then update set 
  dest.ename    = src.ename,
  dest.sal      = src.sal,
  dest.hiredate = src.hiredate,
  dest.job      = src.job,
  dest.deptno   = src.deptno,
  dest.comm     = src.comm,
  dest.mgr      = src.mgr
  -- DELETE ROWS IN TARGET TABLE IF SOURCE DEPTNO EQUALS 30
  delete where deptno = 30
when not matched then 
  insert (empno, ename, sal, comm, mgr, job, hiredate, deptno)
  values (src.empno, src.ename, src.sal, src.comm, src.mgr, src.job, src.hiredate, src.deptno)
  -- ONLY IF DEPTNO IN SOURCE DATA NOT EQUALS "30"
  where not src.deptno = 30
/
So kann man in einem Ladeprozeß auch Zeilen mit einem Löschflag versehen und mit MERGE dann gezielt löschen lassen. Wie man sieht, ist MERGE ein extrem mächtiges Kommando - viele Ladevorgänge lassen sich so u.U. mit einem einzigen SQL-Kommando erledigen ... und das ist allemal besser als selbstprogrammierte PL/SQL-Schleifenkonstrukte. Wenn man eine Operation auf größeren Datenbeständen mit reinem SQL lösen kann, ist das nahezu in allen Fällen besser, wenn man reines SQL anstelle von PL/SQL Loops verwendet. Dazu muss ich vielleicht auch mal ein Blog-Posting schreiben ...
Mehr Informationen (inkl. Syntax-Charts) findet Ihr in der Dokumentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#i2081218
So one can set a delete flag in the source data - after the MERGE operation those rows are removed from the target table. MERGE can be an extremely powerful statement. Depending on the source data rows in the target table might be inserted, updated or deleted. Operations which otherwise would require procedural (PL/SQL) logic can now be achieved with just one SQL MERGE operation. And that's a general recommendation: If an operation (on large amounts of data) can be achieved with a single SQL operation this is in almost every case better than coding procedural logic. Perhaps I should write a blog posting also about this ...
More information can be found in the database documentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#i2081218

Kommentare:

Anonym hat gesagt…

Vielen Dank!!
Mit dieser Funktion kann man sicher viel Zeit und Arbeit sparen.

Matthias hat gesagt…

Hallo,

vielen Dank für den Artikel. Leider funktioniert MERGE nicht, wenn die Tabelle Domain-Indizies nutzt. Es wird dann ein ORA-29886 geworfen. Zumindest habe ich diese Erfahrung unter 10g gemacht.

Viele Grüße,
Matthias

Anonym hat gesagt…

Hallo,

kannst Du eine Aussage zur Performance von MERGE gegenüber UPDATE > DUP_VAL_ON_INDEX > INSERT machen? Gerade bei Massendaten ist die Geschwindigkeit oft ausschlaggebend.

Danke und Gruß, Harald

Carsten Czarski hat gesagt…

Hallo Matthias,

ein Grund, auf 11g zu gehen ;-)
http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07402

"The SQL MERGE statement can now be used with a domain index."

Beste Grüße

-Carsten

Carsten Czarski hat gesagt…

Zu "Anonym" und der Performance ...

Ein MERGE sollte auf jeden Fall schneller sein; denn das INSERT -> DUP_VAL_ON_INDEX -> UPDATE erfordert eine PL/SQL Loop und entsprechende Context Switches zwischen SQL und PL/SQL - da sollte ein SQL MERGE (der allein im SQL Context) stattfindet, immer schneller sein ...

--- Ausprobieren ... ;-)

-Carsten

Dirk hat gesagt…

Moin!
Kompliment für das immer interessante Blog, und danke für diesen Post - hat mir so manchen Loop erspart... ;-)

Viele Grüße
Dirk

Anonym hat gesagt…

Hallo Carsten,

könntest du ein Beispiel mit einem komplexeren ON-(Match-Kriterium) darstellen, z.B. mit mehreren Spalten?

Gruß aus Hamburg

Beliebte Postings