Constraints
Constraints=Deferred
Noconstraints
Specifies when or if constraints are evaluated for data
being loaded. If you specify the Constraints qualifier,
constraints are evaluated as each record is loaded. If you
specify the Noconstraints qualifier, constraints are not
evaluated at all during the load operation. If you specify the
Constraints=Deferred qualifier, constraints are evaluated after
all data from the input file has been loaded.
The default is the Constraints qualifier.
Oracle Corporation recommends that you accept the default for
most load operations. The Noconstraints and Constraints=Deferred
qualifiers are useful when load performance is your highest
priority, you fully understand the constraints defined for
your database, and you are familiar enough with the input data
to be fairly certain that loading that data will not violate
constraints; then you might use these qualifiers as follows:
o Constraints=Deferred
This qualifier is particularly useful for improving
performance when you are loading data into a new table.
Oracle Corporation strongly recommends that you issue an
RMU Verify command with the Constraints qualifier when the
load operation has completed. Note, however, that issuing the
RMU Verify command after the load operation has completed
takes about the same amount of time that would have been
spent had you specified the RMU Load command with the
Constraints qualifier. In other words, by specifying the
Constraints=Deferred qualifier, you are only delaying when
the constraint verification will take place.
o Noconstraints
This qualifier is particularly useful when you are performing
a parallel load operation with the Defer_Index_Updates
qualifier. Oracle Corporation strongly recommends that you
issue an RMU Verify command with the Constraints qualifier
when the load operation has completed. Note, however, that
when you issue the RMU Verify command with the Constraints
qualifier, all rows in the table are checked for constraint
violations, not just the rows that are loaded.
Consider the following before issuing an RMU Load command with
the Noconstraints or Constraints=Deferred qualifier:
o If a table is populated with data prior to a load operation,
it is less expensive to check constraints on each record
as it is being loaded, than to verify constraints on the
entire table after the set of new records has been loaded.
For example, assume you load 200 new records into a table that
currently holds 2,000 records and one constraint is defined
on the table. If you verify constraints as the records are
being loaded, constraint validation is performed 200 times.
If you wait and verify constraints after the load operation
completes, constraint verification must be performed for 2,200
records
o If an RMU Verify command reveals that constraint violations
occurred during the load operation, you must track down those
records and either remove them or make other modifications
to the database to restore the data integrity. This can be a
time-consuming process.
Also consider a situation where all of the following are true:
o You perform a parallel load operation
o You specify the Constraints qualifier
o The table into which you are loading data has a constraint
defined on it
o The constraint defined on the table was defined as deferred
o Constraint evaluation fails during the load operation
In a case such as the preceding, you can not easily determine
which rows were loaded and which were not. Therefore Oracle
Corporation recommends that if deferred constraints are defined
on a table, then you should also specify the Constraints=Deferred
qualifier in your parallel load command. When you follow this
recommendation, the records that violate the constraint are
stored in the database. When the load operation completes, you
can remove from the database those records that violate the
constraint.
See Example 6 in Verify for an example of the steps to take if
an RMU Verify command reveals that an RMU Load command has stored
data that violates constraints into your database.