Enhanced External Table Functionality

Page 143

The New on Condition

You can now insert all rows that are in the source table into the destination table without having to join the source and target tables. This is done using the merge command together with the new on constant filter predicate. Here is an example:

 MERGE INTO new_sales ns USING sales s ON (1=0) WHEN NOT MATCHED THEN INSERT (ns.store_id, ns.sale_date, ns.upc, ns.new_sale_amt) VALUES (ns.store_id, ns.sale_date, ns.upc, ns.sale_amt); 

In this case, the entire contents of the SALES table will be inserted into the NEW_ SALES table.

Optional delete Clause in the merge Statement

During data operations, there may be times when you will want to perform data cleansing. Oracle Database 10g allows you to include an optional delete clause in the merge statement so that you can remove rows that may have become obsolete. Here is an example:

 MERGE INTO emp_history eh USING emp_history_adds eha ON (eh.empno =  eha.empno) WHEN MATCHED THEN UPDATE SET eh.total_pay=eh.total_pay+eha.net_pay, eh.update_date=sysdate WHERE eh.pay_code <> 'Retired' DELETE WHERE (eha.pay_code = 'DELETE') WHEN NOT MATCHED THEN INSERT (eh.empno, eh.total_pay, eh.update_date) VALUES (eha.empno, eha.net_pay, SYSDATE) WHERE eha.pay_code <> 'Retired'; 

Enhanced External Table Functionality

Oracle Database 10g offers some new external table functionality. This includes the capability to write to an external table, perform parallel external table operations, and indicate that only referenced columns should be projected, which can eliminate failures during external table select operations due to data quality problems. Let's look at these features in a bit more detail next.

Writing to External Tables

Oracle Database 10g now allows you to write to an external table, enabling you to move data from within the database to an external flat file outside of the database.