Method
of Saving Data
Conventional path loads use SQL processing and a database COMMIT
operation for saving data. The insertion of an array of records is followed by
a COMMIT operation. Each data load may involve several transactions.
Direct path loads use data saves to write blocks of data to Oracle data
files. This is why the direct path loads are faster than the conventional ones.
The following features differentiate a data save from COMMIT:
•
During a data save, only full database blocks are
written to the database.
•
The blocks are written after the high-water mark
(HWM) of the table.
•
After a data save, the HWM is moved.
•
Internal resources are not released after a data
save.
•
A data save does not end the transaction.
•
Indexes are not updated at each data save.
Note: Direct path
and parallel direct path loads are so similar (regarding DML activities) that
they are not separated in this comparison.
Logging
Changes
Conventional
path loading generates redo entries that are similar to any DML statement. When
using a direct path load, redo entries are not generated if:
•
The
database is in NOARCHIVELOG mode
•
The
database is in ARCHIVELOG mode but logging is disabled
(Logging can be disabled by setting the NOLOGGING attribute for the table or by using the UNRECOVERABLE clause in the control file.)
(Logging can be disabled by setting the NOLOGGING attribute for the table or by using the UNRECOVERABLE clause in the control file.)
Enforcing
Constraints
During a
conventional path load, all enabled constraints are enforced in the same way
that they are during any DML operation.
During
direct path loads, the constraints are handled as follows:
•
NOT
NULL constraints are checked when arrays are built.
•
FOREIGN
KEY and CHECK constraints are disabled, and they can be enabled at the end of
the load by using the appropriate commands in the control file. The FOREIGN KEY
constraints are disabled because they reference other rows or tables, and the CHECK
constraints are disabled because they may use SQL functions. If only a small
number of rows are to be inserted into a large table, use conventional loads.
•
PRIMARY
KEY and UNIQUE constraints are checked during and at the end of the load, and
they can be disabled if they are violated.
Firing
the INSERT Triggers
The WHILE INSERT
triggers are fired during conventional loads; they are disabled before a direct
path load and reenabled at the end of the load. They may remain disabled if a
referenced object is not accessible at the end of the run. Consider using
conventional path loads to load data into tables with the INSERT triggers.
Loading
into Clustered Tables
Direct
loads cannot be used to load rows into clustered tables. Clustered tables can
be loaded with conventional path loads only.
Locking
While a
direct path load is in progress, other transactions cannot make changes to the
tables that are being loaded. The only exception to this rule is when several
parallel direct load sessions are used concurrently.
