ARTICLES

Oracle's Merge Statement as an ETL Tool

By John Adolph Palinski

Oracle added an extremely useful tool to its 9i+ database versions.  This tool is the Merge statement.  The statement allows the developer to Insert and Update a target table using the same result set without having to use a Where clause statement to locate the Updated record.  This tool greatly simplifies the refresh of data warehouse tables.  The statement is also much simpler and enhances performance.

Prior to this statement, refreshes that required Inserts and Updates require a PL/SQL code block with various cursors, loops, and If-Then structures.  Listing 1 depicts a typical PL/SQL code block that refreshes a table.  The script has the following components:

-  A For Loop that retrieves the fresh data into the PL/SQL code block
-  A Cursor opened for each row fectched by the For Loop.  This cursor tests the target data warehouse table for record existence
-  Cursor Open, Fetch, and Close statements.
-  An If-Then-Else construct that tests the success of the cursor fetch
-  An Update statement
-  An Insert statement

The result of the script is that records that exist in the data warehouse table are updated and the records that do not exist are inserted.

Listing 1 - PL/SQL code block that refreshes a data warehouse table with Inserts and updates

begin
  for new_data in (select * from emp) -- Brings in the current records
  loop
    declare
       cursor test is select * 
                      from emp_data_warehouse
                      where emp_data_warehouse.empno = new_data_empno;
       test_var    test@rowtype;
    begin
       open test;           -- Executes a Select statement against the target data warehouse
       fetch test into test_var;          
       if test%found then    - Determines whether the new record exists in the data warehouse
           update emp_data_warehouse     -- Update the record if it exists
                 set emp_data_warehouse = new_data
           where emp_data_warehouse.empno = new_data.empno;
       else
           insert into emp_data_warehouse -- Insert the record it it doesn't exist
               (emp_data_warehouse)
           values
               (new_data);
       end if;
       close test;
     end;
     end loop;
end;
/         


The Merge statement simplifies the code needed to perform the insert/update.  The statement increases performance by:

-  Eliminating the need to open a cursor for each row checking for existence
-  Having to locate and lock each updated record

The Merge statement eliminates the tasks since its first step is to join the new data set records with the target data warehouse data set records.  Oracle uses the power of a join to identify whether a record exists or not.  The Merge statement allows the developer to perform different actions when a match or non-match occurs.  Joining and matching result sets eliminates much of the incremental database hits caused by opening the cursor and issuing the update statement.  

Listing 2 illustrates a merge statement that performs the same insert/update as the Listing 1 PL/SQL code block.

Listing 2 - Using the Merge statement to refresh a data warehouse table

merge into emp_data_warehouse           -- Identifies the target table
using emp                               -- Identifies the source table, view, or in-line view
on (emp_data_warehouse.empno = emp.empno) -- Join condition
when matched then                      -- Reviews the joined result set and tests for a match
  update                       -- Notice the Update statement doesn't have a data source name
    set emp_data_warehouse.ename = emp.ename  -- No Where clause is needed
when not matched then  -- Review the joined result set and tests for a match
  insert
    (emp_data_warehouse.empno, emp_data_warehouse.ename)
  values
    (emp.empno, emp.ename)

Notice that there are far fewer lines in the Merge statement.  It is also much simpler.  The following is a template of the statement:

merge into refreshed_data_source
using (refreshed_from_data_source)
on (matching conditions)
when matched then
  statement
when not matched then
  statement

Some points to consider are: