索引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，如果對一個索引執行失效命令，也可能會遇到這個錯誤，原因是一樣的。
因此，修改你的命令就可以啦~~