Advantages of a Direct Path Load

A direct path load is faster than the conventional path for the following reasons:

  • Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.

  • SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is reduced.

  • A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.

  • A direct path load uses multiblock asynchronous I/O for writes to the database files.

  • During a direct path load, processes perform their own write I/O, instead of using Oracle's buffer cache. This minimizes contention with other Oracle users.

  • The sorted indexes option available during direct path loads enables you to presort data using high-performance sort routines that are native to your system or installation.

  • When a table to be loaded is empty, the presorting option eliminates the sort and merge phases of index-building. The index is filled in as data arrives.

  • Protection against instance failure does not require redo log file entries during direct path loads. Therefore, no time is required to log the load when:

    • The Oracle database has the SQL NOARCHIVELOG parameter enabled

    • The SQL*Loader UNRECOVERABLE clause is enabled

    • The object being loaded has the SQL NOLOGGING parameter set

    See "Instance Recovery and Direct Path Loads".