Oracle7 Server Utilities
Direct Loads, Integrity Constraints, and Triggers
With the conventional path, arrays of data are inserted with standard SQL statements -- integrity constraints and insert triggers are automatically applied. But when loading data on the direct path, some integrity constraints and all database triggers are disabled. This section discusses the implications of using direct path loads with respect to these features.
Integrity Constraints
During a direct path load, some integrity constraints are automatically disabled. Others are not. For a description of the constraints, see Chapter 5 "Maintaining Data Integrity" of the Oracle7 Server Application Developer's Guide.
Enabled Constraints
The constraints that remain in force are:
Not Null constraints are checked at insertion time. Any row that violates this constraint is rejected. Unique constraints are verified when indexes are rebuilt at the end of the load. The index will be left in direct load state if a violation is detected. (Direct load state is explained ![[*]](jump.gif)
.) A primary key constraint is merely a unique-constraint on a not-null column.
Disabled Constraints
The following constraints are disabled:
- referential constraints (foreign keys)
Reenable Constraints
When the load completes, the integrity constraints will be re-enabled automatically if the REENABLE clause is specified. The syntax for this clause is as follows:
The optional keyword DISABLED_CONSTRAINTS is provided for readability. If the EXCEPTIONS clause is included, 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 the Oracle7 Server SQL Reference for instructions on how to create an exceptions table.
If the REENABLE clause is not used, then the constraints must be re-enabled manually. All rows in the table are verified then. If Oracle finds any errors in the new data, 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. See ENABLE in the Oracle7 Server SQL Reference.
The SQL*Loader log file describes the constraints that were disabled, the ones that were re-enabled and what error, if any, prevented re-enabling of each constraint. It also contains the name of the exceptions table specified for each loaded table.
Warning: As long as bad data remains in the table, the integrity constraint cannot be successfully re-enabled.
Suggestion: 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.
Database Insert Triggers
Table insert triggers are also disabled when a direct path load begins. After the rows are loaded and indexes rebuilt, any triggers that were disabled are automatically re-enabled. The log file lists all triggers that were disabled for the load. There should not be any errors re-enabling triggers.
Unlike integrity constraints, insert triggers are not reapplied to the whole table when they are enabled. As a result, insert triggers do not fire for any rows loaded on the direct path. When using the direct path, the application must ensure that any behavior associated with insert triggers is carried out for the new rows.
Replacing Insert Triggers with Integrity Constraints
Applications commonly use insert triggers to implement integrity constraints. Most of these application insert triggers are simple enough that they can be replaced with Oracle's automatic integrity constraints.
When Automatic Constraints Cannot Be Used
Sometimes an insert trigger cannot be replaced with Oracle's automatic integrity constraints. For example, if an integrity check is implemented with a table lookup in an insert trigger, then automatic check constraints cannot be used, because the automatic constraints can only reference constants and columns in the current row. This section describes two methods for duplicating the effects of such a trigger.
Preparation
Before either method can be used, the table must be prepared. Use the following general guidelines to prepare the table:
1. Before the load, add a one-character column to the table that marks rows as "old data" or "new data".
2. Let the value of null for this column signify "old data", because null columns do not take up space.
3. When loading, flag all loaded rows as "new data" with SQL*Loader's CONSTANT clause.
After following this procedure, all newly loaded rows are identified, making it possible to operate on the new data without affecting the old rows.
Using An Update Trigger
Generally, you can use a database update trigger to duplicate the effects of an insert trigger. This method is the simplest. It can be used whenever the insert trigger does not raise any exceptions.
1. Create an update trigger that duplicates the effects of the insert trigger.
Copy the trigger. Change all occurrences of "new.column_name" to "old.column_name".
2. Replace the current update trigger, if it exists, with the new one
3. Update the table, changing the "new data" flag to null, thereby firing the update trigger
4. Restore the original update trigger, if there was one
Note: Depending on the behavior of the trigger, it may be necessary to have exclusive update access to the table during this operation, so that other users do not inadvertently apply the trigger to rows they modify.
Duplicating the Effects of Exception Conditions
If the insert trigger can raise an exception, then more work is required to duplicate its effects. Raising an exception would prevent the row from being inserted into the table. To duplicate that effect with an update trigger, it is necessary to mark the loaded row for deletion.
The "new data" column cannot be used for a delete flag, because an update trigger cannot modify the column(s) that caused it to fire. So another column must be added to the table. This column marks the row for deletion. A null value means the row is valid. Whenever the insert trigger would raise an exception, the update trigger can mark the row as invalid by setting a flag in the additional column.
Summary: When an insert trigger can raise an exception condition, its effects can be duplicated by an update trigger, provided:
- two columns (which are usually null) are added to the table
- the table can be updated exclusively (if necessary)
Using a Stored Procedure
The following procedure always works, but it is more complex to implement. It can be used when the insert trigger raises exceptions. It does not require a second additional column; and, because it does not replace the update trigger, and it can be used without exclusive access to the table.
1. Create a stored procedure that duplicates the effects of the insert trigger. Follow the general outline given below. (For implementation details, see the PL/SQL User's Guide and Reference for more information about cursor management.)
- declare a cursor for the table, selecting all the new rows
- open it and fetch rows, one at a time, in a processing loop
- perform the operations contained in the insert trigger
- if the operations succeed, change the "new data" flag to null
- if the operations fail, change the "new data" flag to "bad data"
2. Execute the stored procedure using an administration tool such as Server Manager.
3. After running the procedure, check the table for any rows marked "bad data".
4. Update or remove the bad rows.
5. Re-enable the insert trigger.
Permanently Disabled Triggers & Constraints
SQL*Loader needs to acquire several locks on the table to be loaded to disable triggers and constraints. If a competing process is enabling triggers or constraints at the same time that SQL*Loader is trying to disable them for that table, then SQL*Loader may not be able to acquire exclusive access to the table.
SQL*Loader attempts to handle this situation as gracefully as possible. It attempts to re-enable disabled triggers and constraints before exiting. However, the same table-locking problem that made it impossible for SQL*Loader to continue may also have made it impossible for SQL*Loader to finish enabling triggers and constraints. In such cases, triggers and constraints will remain permanently disabled until they are manually enabled.
Although such a situation is unlikely, it is possible. The best way to prevent it is to make sure that no applications are running that could enable triggers or constraints for the table, while the direct load is in progress.
If a direct load is aborted due to failure to acquire the proper locks, carefully check the log. It will show every trigger and constraint that was disabled, and each attempt to re-enable them. Any triggers or constraints that were not re-enabled by SQL*Loader should be manually enabled with the ENABLE clause described in the Oracle7 Server SQL Reference.
Alternative: Partitioned Load
If triggers or integrity constraints pose a problem, but you want faster loading, you should consider a partitioned load. A partitioned load works on a multiple-CPU system. Divide the data set into separate partitions, and then load each part through different CPUs with the conventional path. The resulting load is faster than a single-CPU conventional load, although possibly not as fast as a direct load. But triggers fire, and integrity constraints are applied to the loaded rows.