在管理数据库过程中，我们经常遇到索引失效或者大量删除、插入表数据时暂时让索引失效，那么手动让索引暂时“失效”需要注意什么呢？

点击(此处)折叠或打开

此处需要注意，两条语句执行条件。

disable 方式只能适用于函数索引，而当使用unusable方式时，索引需要rebuild或者直接drop。官方说明如下：

点击( 此处)折叠或打开

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 .

点击( 此处)折叠或打开

ITPub博客

【INDEX】索引失效或者不可用 UNUSABLE