10.3.7.  Direct Path Loads and Integrity Constraints (Pages 191 - 195)

During a conventional path load, any integrity constraints on the table being loaded are verified for each row as it is inserted. If any row violates any of the constraints, it gets rejected. This is simple and intuitive. Direct path loads deal with integrity constraints in a very different way. A direct path load disables some constraints at the beginning of the load, while some others remain enabled throughout. In addition, much of the work of validating new rows against these constraints is deferred until the end of the load.

The fact that SQL*Loader alters constraints when a direct path load is performed has an impact on the privileges that you need in order to load a table. When performing a direct path load, you must have the necessary privileges to enable and disable constraints on the table(s) being loaded. If you are not the table?s owner, this means you need the ALTER TABLE privilege on the corresponding table(s). If you attempt a direct path load without having the privileges needed to enable and disable constraints on the target tables, you will get an error similar to the following:

SQL*Loader_965:	Error 1031 disabling constraint WATERFALLS_CK on table ~IS WATERFALLS

The following constraint types remain enabled throughout a direct path load

  1. NOT NULL constraints

    1. These constraints remain enabled and enforced during a direct path load. Rows violating NOT NULL constraints will be rejected, and will be written to the bad file.

  2. UNIQUE KEYand PRIMARYKEY constraints

    1. These constraints remain enabled during a direct path load, but new rows are only verified against these constraints at the end of the load process when the underlying indexes are rebuilt. If a row violates a UNIQUE KEY or PRIMARY KEY constraint, then it is not rejected; it still gets loaded. However, the corresponding index will remain in the UNUSABLE state after the load completes. You need to locate the duplicate row(s), resolve the problem, and then rebuild the index manually.

    2. If the underlying index for a UNIQUE KEY or PRIMARY KEY constraint is in an unusable state, that doesn?t, in and of itself, disable the constraint.

The following constraint types are disabled during a direct path load. Their validation status will be set to NOT VALIDATED to indicate that Oracle doesn?t know whether they are met by all the rows currently in the table or tables being loaded:

  1. CHECK constraints

    1. You need to ensure that the data you are loading meets any CHECK constraints that you have defined; otherwise, you won?t be able to reenable those CHECK constraints at the end of the load.

    2. Beginning with Oracle9i, CHECK constraints are enforced during a direct path load.

  2. FORFIGN KEY constraints

    1. SQLLoader disables FOREIGN KEY constraints during a load, probably to avoid the overhead of querying related tables in order to check each row that is loaded. Be sure that you load valid data into your FOREIGN KEY columns.

When disabling these constraints, SQL*Loader will write a message such as the following into the log file:

constraint GNIS.WATERFALLS.WATERFALLS_CK was disabled and novalidated before the
constraint ~1IS .WATERFAILS ?ThTERFALL COUNTY_FK was disabled and novalidated before the load.

You can verify the status of constraints on a table by querying the User_constraints (also alL constraints or dba_ constraints) data dictionary view. The query in the following example retrieves the status of the constraints on the table named waterfalls

SQL> SELECT constraint_noire, status, validated
2 FRQ~ user_constraints

3 WHERE table_name ? ?IaPERFMSLS?)

By default, at the end of a direct path load, any constraints that SQL*Loader disabled are left in that disabled state. You?ll need to reenable and revalidate the constraints yourself. Recognizing that few users want to do that manually, Oracle provides a way for you to have SQLtLoader reenable the constraints automatically. You do that through the use of the REENABLE DISABLED_CONSTRAINTS clause, which is part of the INTO TABLE clause. The following LOAD statement specifies this clause:

INFILSE waterfalls. dat

INSERT INK) TABLE taterf ails

SORTED INDEXES (water falls_pk)



(fallsjiane, falls_county, falls_state)

The DISABLED_CONSTRAINTS keyword is an optional noise word that makes the meaning of the clause clear to people who aren?t familiar with it. If you don?t like to type, you can just specify REENABLE.

While SQL?Loader is reenabling previously disabled constraints at the end of a direct path load, it may encounter one or more rows that violate a constraint. If this happens, the violated constraint is enabled, but not validated. SQL*Loader will also write an error message to the log file mentioning the constraint and the reason it couldn?t be validated. The log file also mentions the constraints that were enabled and validated successfully. The following example shows both types of messages:

C~IIS . WATERFALLS. WAT~FALLS_CK was re-enabled.


Constraint QUS .WATERFI?ILS. WATERFALLS_CR was validated
C~IIS .WATERFALLS, WATEPFALLS_COUWW_FK was not re-validated due to ORACLE error


This example indicates that SQLLoader re-enabled both the waterfalls_ck and waterfalls_countyjk constraints. However, it could validate only waterfalls_ck. The constraint waterfalls_countyjk could not be validated because of Oracle error 2298, which is a shortened form of ORA-02298. If you look that error up in the Oracleai Error Messages manual, you?ll find that it?s a ?parent key not found? error. This means at least one row of the data just loaded contains a county name that is not found in the parent table to which the FOREIGN KEY constraint refers. If you query the user_constraints view now, you?ll find that the validated column for the constraint contains a value of NOT_VALIDATED:

SQL> Sfl~CT constraint_name, status, validated

2 FRcM user_constraints

3 W~E table_name - ?1aT~flLLs?3

Because the constraint has not been validated, the Oracle database engine can no longer count on it to represent the true state of the data within the table.

Because of the danger that some constraints may not get revalidated after a direct path load, even though you have specified the REENABLE clause, you must explicitly check the log file, or use the query shown here, to verify the validation status of all constraints on the table(s) that you loaded. You must manually deal with any constraints not automatically revalidated by SQL?Loader.

As you?ve seen, SQL*Loader writes an error message to the log file if it can?t validate a constraint. However, to solve the problem, you really need to know more than just that the problem occurred. You need to know the error that prevented the constraint from being validated, and you also need to know which rows of data led to the error occurring. Using the optional EXCEPTIONS keyword in the REENABLE clause, you can cause the ROWIDs of all problem rows to be inserted into an exceptions table. In the following example, the exceptions table is named waterfalls_exc:


Using the EXCEPTIONS clause in the control file in this way causes SQL*Loader to use the EXCEPTIONS INTO clause of the ALTER TABLE statement when it goes to reenable constraints at the end of a direct path load. Oracle then inserts the ROWID and constraint name into the exceptions table for every constraint violation that it finds. The exceptions table must be in a specific format. You can use the $ORACLE_HOME/rdbms/utlexcptl sql script to create an exceptions table, which you can then rename as you desire.

While the exceptions table gives you the infoimation you need to quickly identify problem rows, it?s still up to you to deal with those rows. In most cases, you?ll want to delete them so that you can revalidate the constraints involved.

For large tables, enabling and validating constraints can be a timeconsuming process. The constraint validation process validates all rows, not just the new ones. In situations where you are adding a small number of rows to an extremely large table, this constraint validation can consume considerably more time than the actual loading of the data. In such a case, Oracle recommends using a conventional path load instead of a direct path load.