[ Pobierz całość w formacie PDF ]
.2.Let the value of null for this column signify old data , because nullcolumns do not take up space.3.When loading, flag all loaded rows as new data with SQL*Loader sCONSTANT clause.After following this procedure, all newly loaded rows are identified, makingit possible to operate on the new data without affecting the old rows.Using An Update TriggerGenerally, you can use a database update trigger to duplicate the effects of aninsert trigger.This method is the simplest.It can be used whenever the inserttrigger 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 one3.Update the table, changing the new data flag to null, thereby firing theupdate trigger4.Restore the original update trigger, if there was oneNote: Depending on the behavior of the trigger, it may be necessary tohave exclusive update access to the table during this operation, so thatother users do not inadvertently apply the trigger to rows they modify.SQL*Loader: Conventional and Direct Path Loads 8-23 Duplicating the Effects of Exception ConditionsIf the insert trigger can raise an exception, then more work is required toduplicate its effects.Raising an exception would prevent the row from beinginserted into the table.To duplicate that effect with an update trigger, it isnecessary to mark the loaded row for deletion.The new data column cannot be used for a delete flag, because an updatetrigger cannot modify the column(s) that caused it to fire.So another columnmust be added to the table.This column marks the row for deletion.A nullvalue means the row is valid.Whenever the insert trigger would raise anexception, the update trigger can mark the row as invalid by setting a flag inthe additional column.Summary: When an insert trigger can raise an exception condition, its effectscan 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 ProcedureThe 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 asecond 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, seePL/SQL User s Guide and Reference for more information about cursormanagement.)" 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 asServer Manager.3.After running the procedure, check the table for any rows marked baddata .4.Update or remove the bad rows.5.Re-enable the insert trigger.8-24 Oracle8 Server Utilities Permanently Disabled Triggers & ConstraintsSQL*Loader needs to acquire several locks on the table to be loaded to disabletriggers and constraints.If a competing process is enabling triggers orconstraints at the same time that SQL*Loader is trying to disable them for thattable, then SQL*Loader may not be able to acquire exclusive access to thetable.SQL*Loader attempts to handle this situation as gracefully as possible.Itattempts to re-enable disabled triggers and constraints before exiting.However, the same table-locking problem that made it impossible forSQL*Loader to continue may also have made it impossible for SQL*Loader tofinish enabling triggers and constraints.In such cases, triggers andconstraints will remain permanently disabled until they are manuallyenabled.Although such a situation is unlikely, it is possible.The best way to prevent itis to make sure that no applications are running that could enable triggers orconstraints for the table, while the direct load is in progress.If a direct load is aborted due to failure to acquire the proper locks, carefullycheck the log.It will show every trigger and constraint that was disabled, andeach attempt to re-enable them.Any triggers or constraints that were not re-enabled by SQL*Loader should be manually enabled with the ENABLEclause described in Oracle8 Server SQL Reference.Alternative: Concurrent Conventional Path LoadsIf triggers or integrity constraints pose a problem, but you want fasterloading, you should consider using concurrent conventional path loads.Thatis, use multiple load sessions executing concurrently on a multiple-CPUsystem.Split the input datafiles into separate files on logical recordboundaries, and then load each such input datafile with a conventional pathload session.The resulting load has the following attributes:" It is faster than a single conventional load on a multiple-CPU system,but probably not as fast as a direct load." Triggers fire, integrity constraints are applied to the loaded rows, andindexes are maintained via the standard DML execution logic.SQL*Loader: Conventional and Direct Path Loads 8-25 Parallel Data Loading ModelsThis section discusses three basic models of concurrency which can be used tominimize the elapsed time required for data loading:" concurrent conventional path loads" inter-segment concurrency with direct path load method" intra-segment concurrency with direct path load methodConcurrent Conventional Path LoadsUsing multiple conventional path load sessions executing concurrently isdiscussed in the previous section.This technique can be used to load the sameor different objects concurrently with no restrictions
[ Pobierz całość w formacie PDF ]
