Oracle Utilities - OCP: Oracle Database 12c Administrator Certified Professional - page 599

NP,Nepal,3
MY,Myanmar,3
BG,Bangladesh,3
VT,The Vatican,1
SQL*Loader Conventional Path Loads, Direct Path Loads
SQL*Loader can use two different methods to load data: either the conventional path or
the direct path. The conventional path load populates tables in the database by executing
SQL INSERT statements. The direct path load formats data blocks and writes the data blocks
directly to the database files, eliminating database overhead.
SQL*Loader does not create tables in the target database, so the target tables must exist
prior to starting the load process. The target table does not have to be empty. To load data
using SQL*Loader, the loading user must have INSERT privileges on the table to be loaded.
If there is data in the table and the intent is to replace duplicate content using the REPLACE
option or empty the table using the TRUNCATE option prior to loading, then the user must
have DELETE privileges on the target table.
Conventional Path Loads
The conventional path load, which is the default for SQL*Loader, uses SQL INSERT state-
ments and an in-memory bind array buffer to load data into database target tables. The input
records are parsed and each field is copied into the bind array until it is full or there are no
more records to process; then the contents of the buffer are inserted into the target table. This
is similar to a normal INSERT operation in that DB resources are used for memory, parsing,
execution, and searching for available space within data blocks.
Direct Path Loads
The direct path load is significantly faster than the conventional path load. A direct path
load parses and converts the input records according to the specifications and builds an
array. The array is then converted into data blocks and written directly to the database.
To load data in direct path load mode using SQL*Loader, set the DIRECT parameter to
TRUE in the parameter file or on the command line.
DIRECT=TRUE
Keep in mind that direct path load does not support loading to clustered tables. Also
during direct path load, any existing indexes are copied and then merged with the new
index keys. For large indexes with few new rows loaded, the time saved by a direct path
load may be less than the time required for index maintenance.
Referential and column-check integrity constraints cannot be applied to rows loaded on
the direct path, so they are disabled during the load and applied to the table when the load
completes. Reenabling the constraints could take longer than the time saved by using direct
path.