Today , i have learned about the enhancements in the oracle 10g merge statement from oracle-developer.net website.Thanks to Adrian Billington , Oracle developer site moderator for sharing this information with us.
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.
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;
SQL> CREATE TABLE tgt AS SELECT * FROM src WHERE ROWNUM <= 80;
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 );
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.
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;
SQL> TRUNCATE TABLE src;
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.