【DB笔试面试352】什么是不可用索引（Unusable Indexes），哪些操作会导致索引变为不可用（unusable）即失效状态？
（一）什么是不可用索引（Unusable Indexes）？对于不可用索引需要注意哪些？
在正常情况下，索引都是可用的。对于可用的索引而言，DBA_INDEXES的STATUS列的值为VALID。当执行某些操作后，例如对索引执行MOVE操作，索引会变为不可用（unusable），即处于失效状态。当索引变为不可用时，Oracle内部会把该索引元数据（即创建语句包含的基本信息)与真实物理数据之间的对应关系撕裂，相关数据块可被重用，相关的索引段会被删除，DBA_INDEXES的SEGMENT_CREATED列的值会变为NO（正常情况下，应为YES），STATUS列的值变为UNUSABLE。对于失效的索引，若想要被重新使用，则只有一种方法，那就是REBUILD，当然也可以先DROP 再CREATE。
对于失效的索引需要注意以下几点：
l 从Oracle 11gR2开始，当索引不可用时，Oracle会清除索引数据（删除相关的索引段），并且不再维护。
l 在本地索引中，可以设置某个分区的索引为UNUSABLE。
l 一般情况下，CBO不考虑使用UNUSABLE状态的索引，包含分区表。相关参数为SKIP_UNUSABLE_INDEXES，其作用如下所示：
| 参数作用 | 控制对UNUSABLE 的非唯一索引，DML操作时是否维护索引数据。 |
| 参数值 | true | false |
|
| 当参数设置为true时（默认），当数据库遇到不可用索引时，只会忽略而不会提示任何错误信息；同时即使该表上带有不可用的索引或索引分区，也可以针对该表执行DML操作，针对不可用索引对应的DML语句都将正常执行，而且数据库停止维护相关索引。 在进行DML（INSERT和UPDATE）操作时，如果是唯一索引，那么无论该参数的值设置为何值，Oracle都会去校验索引的可用性。如果索引不可用，那么就会报错ORA-01502。 |
|
| 当参数设置为false时，DML操作及CBO生成执行计划时都会考虑该索引。所有与UNUSABLE索引相关的操作都会失败，报错信息为：ORA-01502: 索引 'SCOTT.INDEX_T2' 或这类索引的分区处于不可用状态。 |
l 在重建索引的时候一般不允许DML操作，只有加上ONLINE的时候才允许DML操作。
（二）哪些操作会导致索引失效？
当某些操作导致数据行的ROWID改变，索引就会完全失效。可以分普通表和分区表来讨论哪些操作将导致索引失效。
（1）普通表索引失效的情形如下所示：
① 手动置索引无效：ALTER INDEX IND_OBJECT_ID UNUSABLE;。
② 如果对表进行MOVE操作（包含移动表空间和压缩操作）或在线重定义表后，那么该表上所有的索引状态会变为UNUSABLE。MOVE操作的SQL语句为：ALTER TABLE TT MOVE;。
③ SQL*Loader加载数据。
在SQL*Loader加载过程中会维护索引，由于数据量比较大，在SQL*Loader加载过程中出现异常情况，也会导致Oracle来不及维护索引，导致索引处于失效状态，影响查询和加载。异常情况主要有：在加载过程中杀掉SQL*Loader进程、重启或表空间不足等。
（2）分区表索引失效的情形如下所示：
① 对分区表的某个含有数据的分区执行了TRUNCATE、DROP操作可以导致该分区表的全局索引失效，而分区索引依然有效，如果操作的分区没有数据，那么不会影响索引的状态。需要注意的是，对分区表的ADD操作对分区索引和全局索引没有影响。
② 执行EXCHANGE操作后，全局索引和分区索引都无条件地会被置为UNUSABLE（无论分区是否含有数据）。但是，若包含INCLUDING INDEXES子句（缺省情况下为EXCLUDING INDEXES），则全局索引会失效，而分区索引依然有效。
③ 如果执行SPLIT的目标分区含有数据，那么在执行SPLIT操作后，全局索引和分区索引都会被被置为UNUSABLE。如果执行SPLIT的目标分区没有数据，那么不会影响索引的状态。
④ 对分区表执行MOVE操作后，全局索引和分区索引都会被置于无效状态。
⑤ 手动置其无效：ALTER INDEX IND_OBJECT_ID UNUSABLE;。
对于分区表而言，除了ADD操作之外，TRUNCATE、DROP、EXCHANGE和SPLIT操作均会导致全局索引失效，但是可以加上UPDATE GLOBAL INDEXES子句让全局索引不失效。
重建分区索引的命令为：ALTER INDEX IDX_RANG_LHR REBUILD PARTITION P1;。
分区表的索引是否失效情况如下表所示：
|
| 目标分区有数据 | 目标分区无数据 | ||||
| 操作动作 | 操作命令 | 全局索引 | 分区索引 |
| ||
| 是否失效 | 如何避免失效 | 是否失效 | 如何避免失效 |
| ||
| TRUNCATE分区 | ALTER TABLE PT_TRUNC_LHR TRUNCATE PARTITION P1; | 失效 | 操作语句后加UPDATE GLOBAL INDEXES | 没影响 | 对全局索引和分区索引都没有影响 | |
| DROP分区 | ALTER TABLE PT_DROP_LHR DROP PARTITION P1; | 没影响 | ||||
| SPLIT分区 | ALTER TABLE PT_SPLIT_LHR SPLIT PARTITION P_MAX AT (30000) INTO (PARTITION P3,PARTITION P_MAX); | 如果MAX区中已经有记录了，这个时候SPLIT就会导致有记录的新增分区的局部索引失效! | 对局部索引进行重建索引ALTER INDEX IDX_PART_SPLIT_COL3 REBUILD; | |||
| EXCHANGE分区 | ALTER TABLE PT_EX_LHR EXCHANGE PARTITION P1 WITH TABLE T_NM_LHR INCLUDING INDEXES; | 没影响 | 和有数据时一致 | |||
| ALTER TABLE PT_EX_LHR EXCHANGE PARTITION P1 WITH TABLE T_NM_LHR; | 失效 | 对局部索引进行重建索引ALTER INDEX IDX_PART_EX_COL3 REBUILD; | ||||
| ADD分区 | ALTER TABLE PT_ADD_LHR ADD PARTITION P6 VALUES LESS THAN (6666); | 对全局索引和分区索引都没有影响 | ||||
& 说明：
有关数据库操作导致索引失效的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152871/
Oracle之不可见索引 ：http://blog.itpub.net/26736162/viewspace-2124044/
Oracle之虚拟索引 ： http://blog.itpub.net/26736162/viewspace-2123687/
Oracle之不可见索引和虚拟索引的比对：http://blog.itpub.net/26736162/viewspace-2124164/
DB笔试面试历史连接 ：http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
| |
Oracle之不可见索引 ：http://blog.itpub.net/26736162/viewspace-2124044/
Oracle之虚拟索引 ： http://blog.itpub.net/26736162/viewspace-2123687/
https://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes.htm#ADMIN13279
Beginning with Oracle Database 11g Release 2, when you make an existing index unusable, its index segment is dropped.
About Me
.............................................................................................................................................
● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成
● 最新修改时间：2018-05-21 06:00 ~ 24:00
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群 小麦苗的微店
.............................................................................................................................................
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2152871/，如需转载，请注明出处，否则将追究法律责任。
扫一扫
362
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
