DBA Tips Archive for Oracle

  


Using the MERGE Statement

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Example 1
  3. Example 2
  4. Example 3
  5. Example 4



Overview

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.

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

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.



Example 2

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.



Example 3

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



Example 4

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


Copyright (c) 1998-2017 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.

Last modified on
Thursday, 04-Jan-2007 19:37:40 EST
Page Count: 125947