Direct path inserting

In this recipe we will see how to insert many rows in a table using a particular INSERT statement to boost performance.

How to do it...

The following steps demonstrates multiple row insertions in the same INSERT statement:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create an empty table MY_SALES with the SALES table structure:

   CREATE TABLE MY_SALES AS SELECT * FROM SALES WHERE ROWNUM < 1;
   

3. Insert all the rows from SALES table in the newly-created table:

   SET TIMING ON
   INSERT INTO MY_SALES SELECT * FROM SALES;
   COMMIT;
   SET TIMING OFF
   

4. Empty the MY_SALES table:

   TRUNCATE TABLE MY_SALES;
   

5. Insert all of the rows from the SALES table in the newly-created table using direct path inserting:

   SET TIMING ON
   INSERT /*+ APPEND */ INTO MY_SALES SELECT * FROM SALES;
   COMMIT;
   SET TIMING OFF
   

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...