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

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.

As a recovery is not possible from the redo log, you need coordination with the DBA to schedule necessary backup.

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