Direct Load Insert

Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows. Direct Load Insert differs from Conventional Insert in that it bypasses the buffer cache. Of course there are side-effects; keep in mind the following:

• Data is appended to the table. Existing free space is not re-used. So, if you direct load insert a bunch of rows, then delete them, and insert them again, the space made by the delete will not be re-used.
  However if you truncate a table or partition first, then Direct Load Insert will reuse the existing free space.
• Direct Load Insert only works for INSERT INTO .. SELECT .... Inserts using the VALUES clause will use conventional insert.
• Direct Load Insert uses rollback segments to maintain indexes as the data is loaded. The size of your rollback segments will limit the size of the INSERT you can perform. Drop or invalidate the indexes before the load to bypass this problem, or use the Rowid Range technique to work around it.
• Direct Load Insert can be run with a NOLOGGING option making it even faster. This means that in the event of a system crash where the database is restored from a backup taken prior to the INSERT and rolled forward, you will have to re-run your INSERT.
• Direct Load Insert locks the table in exclusive mode. No other session can insert, update, or delete data, or maintain any indexes.
• After a Direct Load Insert, the session that performed the Insert may perform another Direct Load Insert on the same table, but may not perform any other action (SELECT, UPDATE, DELETE, or conventional INSERT) on that table until the transaction is committed.
• Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
• Direct Load Insert cannot occur on:
• Index Organised Tables
• Tables with LOB Columns
• Tables with Object Columns
• Clustered Tables

Attempts to Direct Load Insert on these tables will run in conventional mode without warnings or errors.

To use Direct Load Insert, add the APPEND hint to your INSERT statement.

INSERT /*+ APPEND*/
INTO my_table
SELECT * FROM my_other_table

To make sure your SQL is using Direct Load Insert, run it through Explain Plan. You should see a line reading LOAD AS SELECT.