oracle-developer.net

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.

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.

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.

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".

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!

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.

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'.

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;

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.

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.

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.