Avoiding Index Maintenance - Oracle 8i

For both the conventional path and the direct path, SQL*Loader maintains all existing indexes for a table.

Index maintenance can be avoided by using one of the following methods:

  • Drop the indexes prior to the beginning of the load.
  • Mark selected indexes or index partitions as Index Unusable prior to the beginning of the load and use the SKIP_UNUSABLE_INDEXES parameter.
  • Use the SKIP_INDEX_MAINTENANCE parameter (direct path only, use with caution.)
  • Avoiding index maintenance minimizes the amount of space required during a direct path load, for the following reasons:

  • You can build indexes one at a time, reducing the amount of sort (temporary) segment space that would otherwise be needed for each index.
  • Only one index segment exists when an index is built, instead of the three segments that temporarily exist when the new keys are merged into the old index to make the new index.

Avoiding index maintenance is quite reasonable when the number of rows to be loaded is large compared to the size of the table. But if relatively few rows are added to a large table, then the time required to resort the indexes may be excessive. In such cases, it is usually better to use the conventional path load method, or to use the SINGLEROW option of SQL*Loader.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 8i Topics