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

Get Oracle Database 11gR2 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.