Other Optimizations - Oracle Database 11gR2 Performance Tuning - page 346

6. Show the execution plan for classical INSERT statement:

EXPLAIN PLAN FOR INSERT INTO MY_SALES SELECT * FROM SALES;
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY(null,null,'TYPICAL -BYTES'));

7. Show the execution plan for direct path insert statement:

EXPLAIN PLAN FOR INSERT /*+ APPEND */ INTO MY_SALES
SELECT * FROM SALES;
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY(null,null,'TYPICAL -BYTES'));

8. Clean the database:

DROP TABLE MY_SALES;

How it works...

In step 2 we create a table MY_SALES with the same structure as the SALES table in the SH
schema. In step 3 we execute an INSERT into the MY_SALES table, populating it with all of
the rows in the SALES table (about 1 million rows). In the following screenshot we can see the
results obtained:

In step 4 we truncate the MY_SALES table, and then execute the same INSERT statement
as above, using the /*+ APPEND */ hint, forcing the database to use direct path insertion to
execute the statement.