We can see that MERGE performed less well than our two-part SQL solution; with it taking over twice as long. We can repeat the test against a typical PL/SQL-coded merge (common in older applications).
We will replace the two-part SQL solution with a PL/SQL loop that will attempt an update first and insert only if the update affects no rows.
In versions prior to 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL.
We will compare MERGE to these methods later in this article.
The alternative to this would be to insert first and only update when a DUP_VAL_ON_INDEX exception was raised (a primary or unique key is required for this to work).
MERGE is useful for combining larger source and target datasets, particularly for slowly-changing dimensions in data warehouses.
We will pause the statistics between the two runs to reset the data. MERGE 2 INTO target_table tgt 3 USING source_table src 4 ON ( src.object_id = tgt.object_id ) 5 WHEN MATCHED 6 THEN 7 UPDATE 8 SET tgt.object_name = src.object_name 9 , tgt.object_type = src.object_type 10 WHEN NOT MATCHED 11 THEN 12 INSERT ( tgt.object_id 13 , tgt.object_name 14 , tgt.object_type ) 15 VALUES ( src.object_id 16 , src.object_name 17 , src.object_type ); We will now run a bulk update and insert as separate SQL statements.
The update is written as an updateable in-line view which is often the fastest technique for bulk updating one table from another.
Without the outer join, Oracle would need to implement a "two-pass" solution such as we might code ourselves with a separate INSERT and UPDATE statement.
Major performance gains will be achieved by tuning the source-target join (for example, using indexes, hints, partitioning etc) or by tuning the USING clause if it is a complex in-line view.