摘自MOS文章Why index became invalid? (文档 ID 1576700.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
GOAL
Indexes should have status as VALID to become usable by queries however sometimes it may have status as UNUSABLE and this note explains those causes.
SOLUTION
Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild. These DBA tasks that shift table ROWID's include:
Table partition maintenance - Alter commands (move, split or truncate partition) will shift ROWID's, making the index invalid and unusable.
CTAS maintenance - Table reorganization with "alter table move" or an online table reorganization (using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes.
Oracle imports - An Oracle import (imp utility) with the skip_unusable_indexes=y parameter
SQL*Loader (sqlldr utility) - Using direct path loads (e.g. skip_index_maintenance) will cause invalid and unusable indexes