Oracle Database Administration: The Essential Refe

Conventional and Direct Path Loading

Data can be loaded into the database using either the conventional path or direct path methods. When using conventional path loading , data is loaded into a bind array, which is then written to the database when the bind array is full, or when there is no data remaining in the input file. Conventional path loading uses the standard SQL INSERT command to place data into the database.

When using direct path loading , data rows are placed into formatted database blocks and are then written directly to the database. This process bypasses most RDBMS processing, and results in a significantly faster load. However, there are several restrictions associated with direct path loading:

• Tables and associated indexes will be locked for the duration of the load.

• SQL*Net access is available only under limited circumstances, and its use will slow performance.

• Clustered tables cannot be loaded using direct path.

• Constraints that depend on other tables are disabled during the load and are then applied to the table when the load is completed.

• SQL functions are not available when using the direct path.

• Only the version of SQL*Loader that exactly matches the database version can be used for direct path loads; for example, you cannot use SQL*Loader Version 8.0.3 to load data into an Oracle database running Version 8.0.4.