Index Maintenance Options - Oracle 8i

There are two Oracle8i index maintenance options available default is NO):

  • SKIP_INDEX_MAINTENANCE={YES | NO}
  • SKIP_UNUSABLE_INDEXES={YES | NO}

SKIP_INDEX_MAINTENANCE
SKIP_INDEX_MAINTENANCE={YES | NO} stops index maintenance for direct path loads but does not apply to conventional path loads. It causes the index partitions that would have had index keys added to them instead to be marked Index Unusable because the index segment is inconsistent with respect to the data it indexes. Index segments that are not affected by the load retain the Index Unusable state they had prior to the load.

The SKIP_INDEX_MAINTENANCE option:

  • Applies to both local and global indexes
  • Can be used (with the PARALLEL option) to do parallel loads on an object that has indexes
  • Can be used (with the PARTITION keyword on the INTO TABLE clause) to do a single partition load to a table that has global indexes
  • Puts a list (in the SQL*Loader log file) of the indexes and index partitions that the load set into Index Unusable state

SKIP_UNUSABLE_INDEXES
The SKIP_UNUSABLE_INDEXES option applies to both conventional and direct path loads.

The SKIP_UNUSABLE_INDEXES=YES option allows SQL*Loader to load a table with indexes that are in Index Unusable (IU) state prior to the beginning of the load. Indexes that are not in IU state at load time will be maintained by SQL*Loader. Indexes that are in IU state at load time will not be maintained but will remain in IU state at load completion.

However, indexes that are UNIQUE and marked IU are not allowed to skip index maintenance. This rule is enforced by DML operations, and enforced by the direct path load to be consistent with DML.

Load behavior with SKIP_UNUSABLE_INDEXES=NO differs slightly between conventional path loads and direct path loads:

  • On a conventional path load, records that are to be inserted will instead be rejected if their insertions would require updating an index.
  • On a direct path load, the load terminates upon encountering a record that would require index maintenance be done on an index that is in unusable state.

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

Oracle 8i Topics