When using the APPEND hint, you are telling the optimizer to use direct-path INSERT.
Serialized inserts:
Simply specify the APPEND hint in each INSERT statement, either immediately after the INSERT keyword or immediately after the SELECT keyword in the subquery of the INSERT statement.
INSERT /*+ APPEND */ INTO sales
SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount
FROM sales_activity_direct;
Parallelized inserts:First, enable parallel DML in your session:
ALTER SESSION ENABLE PARALLEL DML;Then, specify the APPEND hint with the PARALLEL hint:
INSERT /*+ APPEND PARALLEL */ INTO sales
SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount
FROM sales_activity_direct;
You can use NOLOGGING with APPEND to make the process even faster. NOLOGGING allows the direct-path INSERT operation to generate a negligible amount of REDO: INSERT /*+ APPEND PARALLEL NOLOGGING */ INTO sales
SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount
FROM sales_activity_direct;
Note:
* If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging setting.
* Direct-path INSERT is the default insert method when running in parallel mode, but unless you have specified the DEGREE attribute for your table, you still need to use the PARALLEL hint for each insert operation.
* Oracle Database performs index maintenance at the end of direct-path INSERT operations on tables that have indexes.
