索引unusable|disable|enable

 

ORACLE使索引变成不可用的状态：
alter index index_name unusable;
执行成功后，如果后续需要再用到该索引的话，就必须重建。重建后会自动变成usable。
根据ORACLE官方文档的说法(An unusable index must be rebulit , or dropped and re-created , before it can be used.)
重建有两种方式
1. rebuild
   alter index index_name rebuild;
2. drop掉该索引，然后再重建。
   drop index index_name;
   create index index_name on xxxxx;
实际上这两种操作的结果是一样的，都是删除再重新启用，不过rebulid方式更为快捷和简单。

另外，数据库还有两种修改INDEX状态的语句，叫disable和enable;

1. enable index
   alter index index_name enable;
2. disable index
   alter index index_name disable;

两者的区别是：enable和disable仅仅只针对函数索引。
ORACLE官方文档提供的说法是：

ENABLE Clause

Enable applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

The function is currently valid

The signature of the current function matches the signature of the function when the index was created

The function is currently marked asDETERMINISTIC

Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.

DISABLE Clause

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

UNUSABLE Clause

Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.

Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.

如果发现一个索引失效以后，对其使用enable命令，可能会引发ORA-02243的错误，这是由于ENABLE只针对函数索引有效，可以试试rebuild，如果对一个索引执行失效命令，也可能会遇到这个错误，原因是一样的。
因此，修改你的命令就可以啦~~