The Oracle Merge Command

Say you would like to take transformed data and use it to update our dimension table. In the past, we'd have accomplished this by a combination of INSERT and UPDATE statements, but starting with Oracle 9i we've now got the Oracle MERGE command which does this in one step. What's more, we can wrap up the whole transformation process into this one Oracle MERGE command, referencing the external table and the table function in the one command as the source for the MERGED Oracle data.

        alter session enable parallel dml;
    merge /*+ parallel(contract_dim,10) append */
        into contract_dim d
        using TABLE(trx.go(
            CURSOR(select /*+ parallel(contracts_

file,10) full (contracts_file) */ *
                from contracts_file ))) f
        on  d.contract_id = f.contract_id
        when matched then
            update set   
                                   desc  = f.desc,
                       init_val_loc_curr = f.init_val_loc_curr,
                       init_val_adj_amt  = f.init_val_adj_amt
        when not matched then
            insert values ( f.contract_id,
                            f.desc,
                            f.init_val_loc_curr,
                            f.init_val_adj_amt); 
           

So there we have it - our complex ETL function all contained within a single Oracle MERGE statement. No separate SQL*Loader phase, no staging tables, and all piped through and loaded in parallel.

Enhanced Merge Functionality

In Oracle Database 10g, the MERGE statement has been extended to cover a larger variety of complex and conditional data transformations, allowing faster loading of large volumes of data.

You should use the MERGE statement to select rows from one or more sources for insert or update of one or more tables. The decision to perform an insert or update is based on conditions specified by you.

The MERGE statement is designed to combine multiple operations to reduce the complexity of mixed insert and update operations. MERGE allows you to avoid multiple INSERT, UPDATE, and DELETE DML statements by combining the operations into a single statement. MERGE is what is known as a deterministic statement. That is, you can only update the same row of the target table once in the same MERGE statement.

Prerequisites

Since MERGE combines INSERT and UPDATE operations, you must have the INSERT and UPDATE object privileges on the target table, and of course, the SELECT object privilege on the source table. If you need to specify the DELETE clause of the merge_update_clause, then you must also have the DELETE object privilege on the target table.

Merge Syntax

The syntax of the MERGE statement is:

MERGE [hint] INTO [schema .]table [t_alias]
USING [[schema .]table | view | subquery] t_alias
ON ( condition ) [merge_update_clause | merge_insert_clause]

merge_update_clause:

WHEN MATCHED THEN UPDATE SET [ = [|DEFAULT][,]]
DELETE

merge_insert_clause:

WHEN NOT MATCHED THEN INSERT ( [,])
VALUES (|DEFAULT[,])

where_clause:

The clauses in the MERGE statement have the following definitions.

INTO Clause - The INTO clause is used to specify the target table into which you are inserting or updating.

USING Clause - The USING clause specifies the source of the data to be updated or inserted. The source for a MERGE statement can be a table, view, or the result of a subquery.

ON Clause - The ON clause specifies the condition that the MERGE operation uses to determine whether it updates or inserts. When the search condition evaluates to true, Oracle updates the row in the target table with corresponding data from the MERGE source. If no rows satisfy the condition, then Oracle inserts the row into the target table based on the corresponding MERGE source row.

merge_update_clause - The merge_update_clause is used to specify the update column values of the target table. Oracle performs the specified update if the condition of the ON clause is true. As with any normal update, when the update clause is executed, all update triggers defined on the target table are fired.

where_clause - You must specify the where_clause if you want Oracle to execute the update operation only if the specified condition is true. The WHERE condition can apply to either the data source or the target table. If the condition is false, the update operation is skipped when merging the row into the target table.

You can specify the DELETE where_clause to clean up data in a table while the MERGE statement is populating or updating it. The only rows affected by the delete clause of the MERGE statement are those rows in the target table that are updated by the merge operation.

This means the DELETE WHERE condition evaluates the updated value, not the original value of the row. Even if a row of the target table satisfies the DELETE condition but is not included in the data set from the join defined by the MERGE's ON clause, then it is not deleted. If the MERGE statement deletes a row, any delete triggers defined on the target table will be activated for each row deletion.

merge_insert_clause – The WHERE clause can be specified by itself or in concert with the merge_insert_clause. If both are specified, then the order of the clauses is not important.

View Update Restrictions

• You are not allowed to specify DEFAULT when updating a view.
• Any column referenced in the ON condition cannot be updated.

merge_insert_clause

To specify the values used for insert operations the merge_insert_clause is used. The MERGE statement uses the merge_insert_clause when the condition of the ON clause is false. As with any normal insert, if the insert clause is executed, all insert triggers defined on the target table are fired. If the column list after the INSERT keyword is left out, the number of columns in the target table must match the number of values in the VALUES clause.

If you wish to insert all of the MERGE source rows into the table, you should use what is known as a "constant filter predicate" in the ON clause. An example of a constant filter predicate would be:

ON (1=2)

A predicate such as the one shown is recognized by Oracle as a special case, and Oracle makes an unconditional insert of all source rows into the table. The benefit of this approach over just omitting the merge_update_clause, Is that Oracle still must perform a join if the merge_update_clause is left out, while with a constant filter predicate, no join is performed.

You would specify the where_clause when you want Oracle to execute the insert operation only if the specified condition is true. The condition can refer only to the MERGE data source. Oracle will skip the insert operation for all rows where the condition evaluates to false.

You can specify the where_clause by itself or with the merge_update_clause. If both are specified, then they can be in either order.

n Example Merge

The following example is taken from the Oracle documentation for Oracle Database 10g. The example creates a bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses(employee_id)
SELECT e.employee_id FROM employees e, orders o

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1)
 

EMPLOYEE_ID      BONUS

----------- ----------

153        180
154        175
155        170
159        180
160        175
161        170
179        620
173        610
165        680
166        640
164        720
172        730
167        620
171        740