Enhanced Merge Functionality

Page 141

Jonathan Says...

Converting tablespaces with CLOB columns starts to look expensive. Perhaps you should test the relative performance of Data Pump Import before jumping on this option for moving tablespaces across platforms.

Enhanced Merge Functionality

Oracle Database 10g offers new functionality for merge statement operation. New conditional clauses and extensions are now available for use with the merge statement. Also, a new optional delete clause has been added to the merge SQL statement. Let's look at each of these in more detail next.

New merge Conditional Clauses and Extensions

Several new options are available with regard to the execution of the merge statement:

The option to omit either the update or insert clause

The ability to use conditional update and insert clauses

New functionality in the form of the new on condition

The Option to Omit Either the update or insert Clause

Oracle Database 10g now allows you to execute a merge statement in which you can omit either the update or insert clause. Here is an example:

 -- Look at our data first SQL> SELECT * FROM emp_history;      EMPNO  TOTAL_PAY UPDATE_DATE ---------- ---------- -----------          1        100   15-NOV-03          2        200   15-NOV-03 SQL> SELECT * FROM emp_history_adds;      EMPNO    NET_PAY ---------- ----------          1        100          3        300 -- Now, run our merge statement SQL> MERGE INTO emp_history eh