During a direct path load, some integrity constraints are automatically disabled. Others are not. For a description of the constraints, see the information about maintaining data integrity in the Oracle Database Development Guide.
KEY (unique-constraints on not-null columns)
NULL constraints are checked at column array build time. Any row that violates the
NULL constraint is rejected.
UNIQUE constraints remain enabled during direct path loads, any rows that violate those constraints are loaded anyway (this is different than in conventional path in which such rows would be rejected). When indexes are rebuilt at the end of the direct path load,
UNIQUE constraints are verified and if a violation is detected, then the index will be left in an Index Unusable state. See "Indexes Left in an Unusable State".
During a direct path load, the following constraints are automatically disabled by default:
Referential constraints (
You can override the automatic disabling of
CHECK constraints by specifying the
EVALUATE CHECK_CONSTRAINTS clause. SQL*Loader will then evaluate
CHECK constraints during a direct path load. Any row that violates the
CHECK constraint is rejected. The following example shows the use of the
CHECK_CONSTRAINTS clause in a SQL*Loader control file:
LOAD DATA INFILE * APPEND INTO TABLE emp EVALUATE CHECK_CONSTRAINTS FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (c1 CHAR(10) ,c2) BEGINDATA Jones,10 Smith,20 Brown,30 Taylor,40
When the load completes, the integrity constraints will be reenabled automatically if the
REENABLE clause is specified. The syntax for the
REENABLE clause is as follows:
The optional parameter
DISABLED_CONSTRAINTS is provided for readability. If the
EXCEPTIONS clause is included, then the table must already exist and you must be able to insert into it. This table contains the
ROWIDs of all rows that violated one of the integrity constraints. It also contains the name of the constraint that was violated. See Oracle Database SQL Language Reference for instructions on how to create an exceptions table.
The SQL*Loader log file describes the constraints that were disabled, the ones that were reenabled, and what error, if any, prevented reenabling or validating of each constraint. It also contains the name of the exceptions table specified for each loaded table.
REENABLE clause is not used, then the constraints must be reenabled manually, at which time all rows in the table are verified. If the Oracle database finds any errors in the new data, then error messages are produced. The names of violated constraints and the ROWIDs of the bad data are placed in an exceptions table, if one is specified.
REENABLE clause is used, then SQL*Loader automatically reenables the constraint and verifies all new rows. If no errors are found in the new data, then SQL*Loader automatically marks the constraint as validated. If any errors are found in the new data, then error messages are written to the log file and SQL*Loader marks the status of the constraint as
NOVALIDATE. The names of violated constraints and the ROWIDs of the bad data are placed in an exceptions table, if one is specified.
Normally, when a table constraint is left in an
NOVALIDATE state, new data can be inserted into the table but no new invalid data may be inserted. However, SQL*Loader direct path load does not enforce this rule. Thus, if subsequent direct path loads are performed with invalid data, then the invalid data will be inserted but the same error reporting and exception table processing as described previously will take place. In this scenario the exception table may contain duplicate entries if it is not cleared out before each load. Duplicate entries can easily be filtered out by performing a query such as the following:
SELECT UNIQUE * FROM exceptions_table;
Because referential integrity must be reverified for the entire table, performance may be improved by using the conventional path, instead of the direct path, when a small number of rows are to be loaded into a very large table.