Oracle Database - Direct (path insert|load) (/*+ APPEND */)

About

A direct-path insert is also known as:

• direct load

A direct-path insert is a bulk operation which will only bypass redo log generation in three cases :

• the database is in NOARCHIVELOG mode database
• the operation is performed on a table marked as NOLOGGING
• the statement is bulk insert in the form INSERT AS SELECT

The insert /*+ APPEND */ will minimize redo log generation in all cases, as it minimize the amount of UNDO generated. The redo that would otherwise be generated for the UNDO information is not created, but ultimately the logging mode of the target table is what will dictate whether redo is generated for the table or not.

Articles Related

Why it's faster

A conventional path load executes SQL INSERT statements to populate tables. In contrast, a direct path load eliminates much of the database overhead by formatting data blocks and writing them directly to the database files. Direct writes operate on blocks above the high water mark and write directly to disk, bypassing the database buffer cache. Direct reads read directly from disk into the PGA, again bypassing the buffer cache.

Reasons to not set NOLOGGING and /*+ APPEND */ on all insert

Few reasons not to set NOLOGGING and use the direct-path insert /*+ APPEND */ on all inserts :

• the direct path insert writes data above the high water mark for the table, ignoring any free space on the freelists, just as SQL Loader direct path load will. If you delete many of the rows in a table and then INSERT /*+ APPEND */ into it, you will not be reusing any of that space.
• you must commit after a successful direct-path insert before reading from that table in that transaction. This could put commits into your transactions where they don't really belong
• Only one session at a time can direct-path insert into a table. All other modification are blocked. This operation serializes (but you can do parallel direct-path inserts from a single session)

Setting Up for Direct Path Loads

To prepare the database for direct path loads, you must run the setup script, catldr.sql, to create the necessary views. You need only run this script once for each database you plan to do direct loads to. You can run this script during database installation if you know then that you will be doing direct loads.