Why index became invalid? (文档 ID 1576700.1)

最新推荐文章于 2024-04-20 19:05:35 发布

转载最新推荐文章于 2024-04-20 19:05:35 发布 · 351 阅读

文章标签：

#Oracle #index #invalid #unusable

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文摘自MOS文章，适用于Oracle数据库企业版11.2.0.2及更高版本。主要解释了索引应处于VALID状态才能被查询使用，但有时会变为UNUSABLE的原因，如DBA任务改变ROWID值需重建索引，包括表分区维护、CTAS维护、Oracle导入、SQL*Loader等操作。

摘自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