Direct Path Load Operations versus Integrity Constraints, Triggers and Instance Recovery

Recently two different forum notes triggered me to write this post –

Direct-path operation terminology in Oracle is used when a session is reading buffers from disk directly into the PGA(opposed to the buffer cache in SGA). During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. You may prefer to use a direct path load when you have a large amount of data to load quickly and you want to load data in parallel for maximum performance, but there are alternative costs to be aware of.

Below picture and definitions are related wait events from Performance Tuning Guide –
Listing 113 – Scattered Read-Sequential Read-Direct Path Read
* db file sequential read(single block read into one SGA buffer)
* db file scattered read(multiblock read into many discontinuous SGA buffers)
* direct read(single or multiblock read into the PGA, bypassing the SGA)

With the conventional path load method, arrays of rows are inserted with standard sql INSERT statements, integrity constraints and insert triggers are automatically applied. But when you load data with the direct path, SQL*Loader disables some integrity constraints and all database triggers. The constraints that remain in force are:
* PRIMARY KEY (unique-constraints on not-null columns)
and the following constraints are automatically disabled by default:
* CHECK constraints
* Referential constraints (FOREIGN KEY)

To start SQL*Loader in direct path load mode, we set the DIRECT parameter to true on the command line or in the parameter file. SQL*Loader log file describes the constraints that were disabled, the ones that were reenabled, and what error, if any, prevented reenabling or validating of each constraint etc. It also contains the name of the exceptions table specified for each loaded table.

In below demo you may see that sql*loader direct path load
– disables the foreign key constraint,
– ignores the foreign key constraint,
– ignores the trigger
where as direct path insert just ignores the foreign key constraint;
Code Listing 113 – Direct Path Load Demo

Because SQL*Loader writes directly to the database files, all rows inserted up to the last data save will automatically be present in the database files if the instance is restarted. Changes do not need to be recorded in the redo log file to make instance recovery possible. But if an instance failure occurs, the indexes being built may be left in an Index Unusable state. Indexes that are Unusable must be rebuilt before you can use the table or partition. If redo log archiving is not enabled(you are operating in NOARCHIVELOG mode), then media recovery is not possible of course. Also if the database or tablespace is in FORCE LOGGING mode, then direct path operation always produces redo, regardless of the nologging settings.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release

Refences Used :
Oracle® Database Utilities 10g Release 2 (10.2) – Chapter 11 Conventional and Direct Path Loads