DBA Tips Archive for Oracle
|
DBA Tips Archive for Oracle
|
Using the MERGE Statement
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
Typical examples for apply the new MERGE function is in a data-warehousing
environment where tables (typically fact tables) need to be refreshed
periodically with new data arriving from on-line systems. The arriving
data may contain changes to existing rows in a fact table and/or new rows
that may need to be inserted. If, for example, a row in the new data
corresponds to an item that already exists in the table, an UPDATE should be
performed; if the row's primary key does not exist in the table, an INSERT
should be performed. Many times, the source system will not be able to distinguish
between newly inserted or changed information during the extraction phase. During the
transformation phase, especially with complex transformations, it is sometimes
impossible in knowing when data needs to be inserted or updated. All of these scenarios would
therefore require the determination of INSERT versus UPDATE to be done during the
data-loading phase.
Prior to Oracle9i, the typical way to implement this type of functionality was to
write a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops and then deciding, for
each row, what action to take, insert or update. Commonly seen with this approach is a
serious performance degradation. The first method would require multiple data scans while
the second method operates on a "pre-record" bases. With the new SQL MERGE command can
overcome these deficiencies by processing the conditional INSERT -or- UPDATE within a
single atomic statement. The data will only be scanned once, and the appropriate DML
command will be issued. This can be done in serial or in parallel.
Examples
The new SQL MERGE statement INSERTs some rows and UPDATEs others in a single
operation. The decision about whether to update or insert into the target table
is based upon a join condition: rows already in the target table that match the join
condition are updated; otherwise a row is inserted using values from a separate subquery.
Example 1
Example 2
Example 3
Example 4
Copyright (c) 1998-2013 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Oracle9i introduced the SQL MERGE command to combine a sequence of
conditional INSERT and UPDATE commands in a single atomic statement,
depending on the existence of a record. This operation is commonly
known as Upsert functionality. Functionality like this is most often
seen in Data Warehousing applications during the "Extraction, Transformation,
and Loading" (ETL) process.
The following example will MERGE values from the table DEPT_ONLINE into a table called DEPT:
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON
SQL> SELECT * FROM dept_online;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
20 RESEARCH DEV DALLAS
50 ENGINEERING WEXFORD
SQL> MERGE INTO dept d
USING (SELECT deptno, dname, loc
FROM dept_online) o
ON (d.deptno = o.deptno)
WHEN MATCHED THEN
UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT MATCHED THEN
INSERT (d.deptno, d.dname, d.loc)
VALUES (o.deptno, o.dname, o.loc);
3 rows merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
50 ENGINEERING WEXFORD
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON
6 rows selected.
The following example will MERGE values from the table DEPT_ONLINE into a table called DEPT,
similar to Example 1 but using a slightly different syntax:
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON
SQL> SELECT * FROM dept_online;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
20 RESEARCH DEV DALLAS
50 ENGINEERING WEXFORD
SQL> MERGE INTO dept d
USING dept_online o
ON (d.deptno = o.deptno)
WHEN MATCHED THEN
UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT MATCHED THEN
INSERT (d.deptno, d.dname, d.loc)
VALUES (o.deptno, o.dname, o.loc);
3 rows merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 ENGINEERING WEXFORD
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON
6 rows selected.
The following example will MERGE literal values (not values from another table)
using one SQL statement into a table called DEPT. Prior to Oracle9i, this would
need to be accomplished using PL/SQL, but with Oracle9i's MERGE command, this
can all be written within one SQL statement. Keep in mind that this example will
start with a pretty clean DEPT table (DEPTNO 10 - 40). I will then be
merging in a DEPTNO of 50. The first time the MERGE runs, it will insert
the new DEPTNO of 50. The second MERGE example will update the record for DEPTNO
50 since it already exists.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- --------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> MERGE INTO dept a
USING (
SELECT 50 deptno
, 'ENGINEERING' dname
, 'WEXFORD' loc
FROM dual
) b
ON (a.deptno = b.deptno)
WHEN NOT MATCHED THEN
INSERT VALUES (b.deptno, b.dname, b.loc)
WHEN MATCHED THEN
UPDATE SET a.loc = 'WEXFORD, PA';
1 row merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- --------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD
SQL> MERGE INTO dept a
USING (
SELECT 50 deptno
, 'ENGINEERING' dname
, 'WEXFORD' loc
FROM dual
) b
ON (a.deptno = b.deptno)
WHEN NOT MATCHED THEN
INSERT VALUES (b.deptno, b.dname, b.loc)
WHEN MATCHED THEN
UPDATE SET a.loc = 'WEXFORD, PA';
1 row merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- --------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD, PA
In this example, I provide the same demo scenario but using a slightly different
syntax for the MERGE statement. Both provide the same performance benefits
and explain plan.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- --------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> MERGE INTO dept
USING dual ON (dept.deptno = 50)
WHEN NOT MATCHED THEN
INSERT VALUES (50, 'ENGINEERING', 'WEXFORD')
WHEN MATCHED THEN
UPDATE SET dept.loc = 'WEXFORD, PA';
1 row merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- --------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD
SQL> MERGE INTO dept
USING dual ON (dept.deptno = 50)
WHEN NOT MATCHED THEN
INSERT VALUES (50, 'ENGINEERING', 'WEXFORD')
WHEN MATCHED THEN
UPDATE SET dept.loc = 'WEXFORD, PA';
1 row merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- --------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD, PA
Thursday, 04-Jan-2007 19:37:40 EST
Page Count: 83157