Conventional Path Load Versus Direct Path Load - Oracle 8i

SQL*Loader provides two methods to load data: conventional path, which uses a SQL INSERT statement with a bind array, and direct path, which loads data directly into a database.The tables to be loaded must already exist in the database. SQL*Loader never creates tables, it loads existing tables. Tables may already contain data, or they may be empty.

The following privileges are required for a load:

  • You must have INSERT privileges on the table to be loaded.
  • You must have DELETE privilege on the table to be loaded, when using the REPLACE or TRUNCATE option to empty out the table’s old data before loading the new data in its place.

Conventional Path
During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or there is no more data left to read), an array insert is executed.

Note that SQL*Loader stores LOB fields after a bind array insert is done. Thus, if there are any errors in processing the LOB field (for example, the LOBFILE could not be found), the LOB field is left empty.

There are no special requirements for tables being loaded through the conventional path.

Direct Path
A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype, and builds a column array. The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing most RDBMS processing. Direct path load is much fasterthan conventional path load, but entails several restrictions.

Note: You cannot use the direct path load method for LOBs, VARRAYs, objects, or nested tables.

Parallel Direct Path
A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intrasegment parallelism). Parallel direct path is more restrictive than direct path.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 8i Topics