For example, if you have a row-level trigger on the
target table, that trigger will be executed for every row inserted into the table. If possible, disable
the triggers prior to the data load. If the trigger should be executed for every inserted row, you
may be able to do a bulk operation once the rows have been inserted, rather than a repeated
operation during each insert. If properly tuned, the bulk operation will complete faster than the
repeated trigger executions. You will need to be sure that the bulk operations execute for all rows
that have not already been processed by the triggers.
In addition to disabling triggers, you should disable the indexes on the target table prior to
starting the data load. If the indexes are left on the table, Oracle will dynamically manage the
indexes as each row is inserted. Rather than continuously manage the index, drop it prior to the
start of the load and re-create it when the load has completed.
?–
?–
?–
?–
?–
Chapter 8: Database Tuning 265
NOTE
Disabling indexes and triggers resolves most of the performance
problems associated with large table-to-table data migration efforts.
In addition to disabling indexes, you should consider disabling constraints on the table. If the
source data is already in a table in the database, you can check that data for its adherence to your
constraints (such as foreign keys or CHECK constraints) prior to loading it into your target table.
Pages:
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450