merge enhancements in 10g

The MERGE statement was introduced in Oracle 9i as part of Oracle's ETL-enabling technology. It enables us to conditionally update or insert data into a target table and in many cases is more flexible and efficient than previous hand-coded "UPSERT" solutions. With the release of 10g, Oracle has added many enhancements to MERGE, making it a considerably more flexible and powerful statement than its 9i predecessor. For example, MERGE can now UPDATE, DELETE and INSERT with separate conditions for each. It also supports UPDATE-only or INSERT-only operations. We'll examine such improvements in this article.

setup

Throughout this article, we'll be using a source and target table to perform the MERGE examples. The target table will have a subset of the source table's data, meaning that with any MERGE, there will be an "80-20" mix of UPDATEs and INSERTs. Note that the target table is modified to enable NULL in all columns. This will enable us to keep the column lists short for the INSERT operations in the examples.

SQL> CREATE TABLE src AS SELECT * FROM all_objects WHERE ROWNUM <= 100;

Table created.

SQL> CREATE TABLE tgt AS SELECT * FROM src WHERE ROWNUM <= 80;

Table created.

SQL> ALTER TABLE tgt MODIFY
  2  ( owner         NULL
  3  , object_name   NULL
  4  , object_id     NULL
  5  , created       NULL
  6  , last_ddl_time NULL
  7  );

Table altered.

recap: merge in oracle 9i

We'll begin by demonstrating a straight MERGE supported by Oracle 9i. We'll pick an arbitrary operation for UPDATE (in this case setting the OBJECT_NAME to lower case). The DML operations in this example are of little consequence; rather it is the syntax that is important.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9  WHEN NOT MATCHED
 10  THEN
 11     INSERT (tgt.object_id, tgt.object_name)
 12     VALUES (src.object_id, src.object_name);

100 rows merged.

This tells us that we've merged 100 records. From the setup of the data, we'd expect there to be 80 lower-cased object names in the target table. We can check this with a simple regular expression as follows.

SQL> SELECT COUNT(*)
  2  FROM   tgt
  3  WHERE  REGEXP_LIKE(object_name,'[a-z]');

  COUNT(*)
----------
        80

1 row selected.

Now we've revisited the MERGE statement as supported by Oracle 9i, we can move onto the 10g new features and enhancements.

optional insert or update in 10g

In Oracle 10g, either the INSERT or UPDATE operations are optional (you must have one, but not necessarily both, unlike in 9i). This is a critical enhancement and one which makes the MERGE statement much more flexible. In particular, this new feature could make updates with correlated sub-queries a thing of the past as MERGE is easier to code, easier to understand, more flexible, less error-prone and faster. In the following example, we'll MERGE source into target but ignore new rows by leaving out the INSERT operation. Note how much easier this is to code and read than a "double-correlated UPDATE".

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name);

80 rows merged.

The following example demonstrates the opposite by leaving out the UPDATE operation. This is the logical equivalent of a conditional INSERT..SELECT statement.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN NOT MATCHED
  6  THEN
  7     INSERT (tgt.object_id, tgt.object_name)
  8     VALUES (src.object_id, src.object_name);

20 rows merged.

In addition to the conditional INSERT..SELECT above, MERGE also supports unconditional INSERT..SELECT by way of the 1=0 condition which Oracle calls a constant filter predicate. The purpose of this is to avoid a join between source and target. Whether this will draw developers away from coding a straightforward INSERT..SELECT statement will, of course, be a matter of personal preference!

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (1 = 0)    --constant filter predicate to avoid join
  5  WHEN NOT MATCHED
  6  THEN
  7     INSERT (tgt.object_id, tgt.object_name)
  8     VALUES (src.object_id, src.object_name);

100 rows merged.

We can see that the unconditional INSERT..SELECT that this example implies has loaded all records from source to target. The lack of keys or constraints on the demonstration tables has allowed this to happen, though in a real system, this would be likely to fail.

conditional dml

With Oracle 10g, we can can now apply additional conditions to the UPDATE or INSERT operation within a MERGE. This is extremely useful if we have different rules for when a record is updated or inserted but we do not wish to restrict the ON condition that joins source and target together. To explain this another way, any filters added to the ON clause, other than the primary join conditions, could increase the likelihood that a record flips over to the "NOT MATCHED" bucket. This would in all probability be wrong and considered a bug.

We can see this in the following example. We MERGE source to target but only wish to UDPATE or INSERT specific matched records. We simply add a suitable predicate (WHERE clause) to the UPDATE and INSERT statements within the MERGE. This does not affect the join between the source and target data in any way so does not change the nature of the MERGE.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9     WHERE src.object_type = 'PACKAGE'
 10  WHEN NOT MATCHED
 11  THEN
 12     INSERT (tgt.object_id, tgt.object_name)
 13     VALUES (src.object_id, src.object_name)
 14     WHERE src.created > TRUNC(SYSDATE) - 365;

20 rows merged.

We can see that these predicates have restricted the number of records merged.

deleting during a merge

Possibly the most unexpected 10g enhancement to MERGE is the ability to conditionally DELETE rows from the target dataset during an UPDATE operation. There are a couple of important points to note regarding this. First, the DELETE works against conditions on the target data, not the source. Second, the DELETE works only on rows that have been updated as a result of the MERGE. Any rows in the target table that are not touched by the MERGE are not deleted, even if they satisfy the DELETE criteria.

We'll begin with a simple example of the DELETE operation. We'll remove any records that are in both the source and target data (i.e. WHEN MATCHED) that are of OBJECT_TYPE = 'TYPE'.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9     DELETE
 10     WHERE tgt.object_type = 'TYPE'
 11  WHEN NOT MATCHED
 12  THEN
 13     INSERT (tgt.object_id, tgt.object_name)
 14     VALUES (src.object_id, src.object_name);

100 rows merged.

We can now look a bit more closely at the MERGE-UPDATE-DELETE operation and what data it affects. First, we'll populate the target table with new data and add some of the same records to the source data to ensure we have matches.

SQL> TRUNCATE TABLE tgt;

Table truncated.

SQL> TRUNCATE TABLE src;

Table truncated.

SQL> INSERT ALL
  2     WHEN object_type IN ( 'PACKAGE', 'PACKAGE BODY' )
  3     THEN INTO tgt
  4     WHEN object_type IN ( 'PACKAGE', 'PACKAGE BODY' )
  5     AND  object_name LIKE 'DBMS%'
  6     THEN INTO src
  7  SELECT *
  8  FROM   all_objects;

2320 rows created.

SQL> SELECT object_type
  2  ,      COUNT(*)
  3  FROM   src
  4  GROUP  BY
  5         object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
PACKAGE                    374
PACKAGE BODY               367

2 rows selected.

SQL> SELECT object_type
  2  ,      COUNT(*)
  3  FROM   tgt
  4  GROUP  BY
  5         object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
PACKAGE                    816
PACKAGE BODY               763

2 rows selected.

Using ALL_OBJECTS data, we've setup the example such that the source and target tables contain data relating only to packages. The source table has a subset of the target data and only contains packages that begin with 'DBMS'. The target table contains data for all packages. Using this data, we can easily demonstrate the effect of a conditional DELETE. In the following example, we'll MERGE the source table into the target table but include a DELETE of any matched records that have an OBJECT_TYPE = 'PACKAGE'. For simplicity, we'll use an update-only MERGE.

SQL> MERGE
  2     INTO  tgt
  3     USING src
  4     ON   (src.object_id = tgt.object_id)
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET tgt.object_name = LOWER(src.object_name)
  9     DELETE
 10     WHERE tgt.object_type = 'PACKAGE';

741 rows merged.

Remember that the DELETE condition should only be tested against records that match. In this case, the only records that should match are those that begin with 'DBMS' (these were the only packages in the source table). Following the MERGE, therefore, we should have no packages in our target table that begin with 'DBMS'. The records in the target table that did not have a matching source record should remain. We can test this by counting both the number of packages and just those that begin with 'DBMS' as below.

SQL> SELECT SUM( CASE object_type
  2                 WHEN 'PACKAGE'
  3                 THEN 1
  4                 ELSE 0
  5              END ) AS package_records
  6  ,      SUM( CASE
  7                 WHEN object_type = 'PACKAGE'
  8                 AND  object_name LIKE 'DBMS%'
  9                 THEN 1
 10                 ELSE 0
 11              END ) AS dbms_package_records
 12  FROM   tgt;

PACKAGE_RECORDS DBMS_PACKAGE_RECORDS
--------------- --------------------
            442                    0

1 row selected.

As we can see, despite requesting a DELETE for OBJECT_TYPE = 'PACKAGE', we still have 442 records of this type in the target table. As stated, this data does not have a match in the source data and therefore is not subjected to the DELETE.

To demonstrate that we did get some updates, we can look at the data that would have matched during the MERGE but would not have satisfied the DELETE criteria. This time, we expect this to be package body records that begin with 'DBMS'. For this data, we would expect the OBJECT_NAME to be lower-cased as a result of the MERGE. We can look at a small sample as below.

SQL> SELECT object_name
  2  FROM   tgt
  3  WHERE  object_type = 'PACKAGE BODY'
  4  AND    object_name LIKE 'dbms%'
  5  AND    ROWNUM <= 3;

OBJECT_NAME
------------------------------
dbms_aq_exp_queues
dbms_prvtaqip
dbms_prvtaqis

3 rows selected.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, June 2004

oracle-developer.net