Home > Database, Oracle > Using the MERGE Statement on Oracle

Using the MERGE Statement on Oracle

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

Advertisements
Categories: Database, Oracle Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: