SQL Loader - Oracle 10g/11g Data and Database Management Utilities - page 43

There is a price to pay for the additional performance boost that you should be
aware of.

Direct path load pros
• The primary beneit of direct path load is that it is faster than the
conventional path load
• It performs data saves, skipping the database buffer cache
• It doesn't have to wait for the DBWR background process to write to the
database files
• It can be parallelized

Direct path load cons
• It may require more space as direct path load looks for never used blocks
beyond the high water mark. If the user is not aware of this, several bubbles
of partially used blocks may remain below the High Water Mark ( HWM ).
• It allows no concurrent transactions to take place at the target table at while
the load is in progress.
• It doesn't ire triggers.
• It doesn't validate check constraints.
• The character set must be consistent with that of the database; otherwise
character conversion may take place.

In this demo a simple table has been created. This table holds two columns, one is the
id and the other is a varchar2 column. 8 million rows were inserted, and then those
rows were extracted to a text datafile. On the first exercise the load takes place by
means of the conventional path. On the second test, the load is performed by means
of direct path loads. The results are:

Case 1 Case 2 Case 3 Case 4

Path Conventional Direct Conventional Direct

Primary Key No No No No

Extent pre-allocation No No Yes Yes

Time 914s 14.6s 883.2s 13.9s