【Oracle】How Do Indexes Become Unusable

遇到的场景：Oracle数据库的分区表出现UNUSABLE INDEX，下述文档用于解决相关问题。

Symptoms

Description of which operations mark index partitions as INDEX UNUSABLE.
描述那些操作使得索引不可用

Cause

There are six types of maintenance operations and adding a partition to a Hash-Partitioned Table that mark index partitions INDEX UNUSABLE (IU). In all cases, you must rebuild the index partitions when the operation is complete.

有六种类型操作使得索引不可用

Solution

Maintenance operations causing index partitions to become INDEX UNUSABLE (IU):

1. IMPORT PARTITION or conventional path SQL*Loader.

2. Direct-path SQL*Loader leaves affected local index partitions and global indexes in an IU state if it does not complete successfully.

3. Partition maintenance operations like ALTER TABLE MOVE PARTITION.

4. Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION.

5. Partition maintenance operations like ALTER TABLE SPLIT PARTITION.

6. Index maintenance operations like ALTER INDEX SPLIT PARTITION.

7. Adding a Partition to a Hash-Partitioned Table

一种是Direct-path 直插方式，另外一种是分区表添加分区。可能使得索引不可用。

Adding a Partition to a Hash-Partitioned Table

When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.

The following statements show two ways of adding a hash partition to table <Table_Name>. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named <Partition_Name> and is created in tablespace <Tablespace_Name>.

ALTER TABLE <Table_Name> ADD PARTITION;

ALTER TABLE <Table_Name> ADD PARTITION <Partition_Name> TABLESPACE <Tablespace_Name>;

Indexes may be marked UNUSABLE as explained in the following table:

Each of these operations may cause index partitions to be marked IU which will require the index partitions to be rebuilt.

上述英文说明，为什么分区表可能出现索引不可用。

Version 10.2 and later

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, in the case of a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index at the time it executes the maintenance operation DDL statement.

The following operations support the UPDATE INDEXES clause:

*  ADD PARTITION | SUBPARTITION
*  COALESCE PARTITION | SUBPARTITION
*  DROP PARTITION | SUBPARTITION
*  EXCHANGE PARTITION | SUBPARTITION
*  MERGE PARTITION | SUBPARTITION
*  MOVE PARTITION | SUBPARTITION
*  SPLIT PARTITION | SUBPARTITION
*  TRUNCATE PARTITION | SUBPARTITION

Conclusion

如果分区表的索引变成不可用，可以通过
1.重建索引
2.删除重复数据之后，再次重建索引

if Some Maintenance operations causing index partitions to become INDEX UNUSABLE (IU).we can use some methods as follows:

1. recreate the constraint/index.
2. if necessary,remove deplicate data and rebuild index.

ALTER INDEX index_name REBUILD;

how to remove deplicat data. you can visit https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql.