SKIP_UNUSABLE_INDEXES=y, then the Import utility postpones maintenance on all indexes that were set to Index Unusable before the Import. Other indexes (not previously set to Index Unusable) continue to be updated as rows are inserted. This approach saves on index updates during the import of existing tables.
Delayed index maintenance may cause a violation of an existing unique integrity constraint supported by the index. The existence of a unique integrity constraint on a table does not prevent existence of duplicate keys in a table that was imported with
INDEXES=n. The supporting index will be in an
UNUSABLE state until the duplicates are removed and the index is rebuilt.
For example, assume that partitioned table
t with partitions
p2 exists on the import target system. Assume that local indexes
p1_ind on partition
p2_ind on partition
p2 exist also. Assume that partition
p1 contains a much larger amount of data in the existing table
t, compared with the amount of data to be inserted by the export file (
dmp). Assume that the reverse is true for
Consequently, performing index updates for
p1_ind during table data insertion time is more efficient than at partition index rebuild time. The opposite is true for
Users can postpone local index maintenance for
p2_ind during import by using the following steps:
ALTER TABLE t MODIFY PARTITION p2 UNUSABLE LOCAL INDEXES;
imp scott FILE=expdat.dmp TABLES = (t:p1, t:p2) IGNORE=y SKIP_UNUSABLE_INDEXES=y
This example executes the
SKIP_UNUSABLE_INDEXES=y statement before performing the import.
ALTER TABLE t MODIFY PARTITION p2 REBUILD UNUSABLE LOCAL INDEXES;
In this example, local index
p1 will be updated when table data is inserted into partition
p1 during import. Local index
p2 will be updated at index rebuild time, after import.