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

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

点击(此处)折叠或打开

1. --使索引失效的两条语句
   
2. alter index index_name disable;
   
3. 
   
4. 
   
5. alter index index_name unusable

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

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

点击(此处)折叠或打开

1. DISABLE Clause
   
2. 
   
3. 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.

点击(此处)折叠或打开

1. UNUSABLE Clause
   
2. 
   
3. 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.
   
4. 
   
5. Restriction on Marking Indexes Unusable
   
6. You cannot specify this clause for an index on a temporary table.

点击(此处)折叠或打开

1. --使索引生效语句
   
2. 
   
3. alter index index_name enable;
   
4. 
   
5. alter index index_name rebuild

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29487349/viewspace-2141124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29487349/viewspace-2141124/