ITPub博客

【DB笔试面试352】在Oracle数据库中,哪些操作会导致索引失效?--UNUSABLE INDEXES

原创 作者:lhrbest 时间:2018-04-12 19:46:36 0 删除 编辑

【DB笔试面试352】什么是不可用索引(Unusable Indexes),哪些操作会导致索引变为不可用(unusable)即失效状态?





(一)什么是不可用索引(Unusable Indexes)?对于不可用索引需要注意哪些?

正常情况下,索引都是可用的。对于可用的索引而言,DBA_INDEXESSTATUS列的值为VALID。当执行某些操作后,例如对索引执行MOVE操作,索引会变为不可用(unusable),即处于失效状态。当索引变为不可用时,Oracle内部会把该索引元数据(即创建语句包含的基本信息)与真实物理数据之间的对应关系撕裂,相关数据块可被重用,相关的索引段会被删除,DBA_INDEXES的SEGMENT_CREATED列的值会变为NO(正常情况下,应为YES),STATUS列的值变为UNUSABLE。对于失效的索引,想要被重新使用,只有一种方法,那就是REBUILD,当然可以先DROP CREATE

对于失效的索引需要注意以下几点:

Oracle 11gR2开始,当索引不可用时,Oracle会清除索引数据(删除相关的索引段),并且不再维护

l 在本地索引中,可以设置某个分区的索引为UNUSABLE

一般情况下,CBO不考虑使用UNUSABLE状态的索引,包含分区表。相关参数为SKIP_UNUSABLE_INDEXES,其作用如下所示:

参数作用

控制对UNUSABLE 的非唯一索引,DML操作时是否维护索引数据

参数值

true | false

 

当参数设置为true默认当数据库遇到不可用索引时,只会忽略而不会提示任何错误信息;同时即使该表上带有不可用的索引或索引分区,也可以针对该表执行DML操作,针对不可用索引对应的DML语句都将正常执行,而且数据库停止维护相关索引。

在进行DMLINSERTUPDATE操作时,如果是唯一索引,那么无论该参数的值设置为何值,Oracle会去校验索引可用性如果索引不可用,那么就会报错ORA-01502

 

当参数设置为false时,DML操作及CBO生成执行计划时都会考虑该索引。所有UNUSABLE索引相关的操作都会失败,报错信息为:ORA-01502: 索引 'SCOTT.INDEX_T2' 或这类索引的分区处于不可用状态。

在重建索引的时候一般不允许DML操作,只有加上ONLINE的时候允许DML操作。

 

 

(二)哪些操作会导致索引失效?

 

当某些操作导致数据行的ROWID改变,索引就会完全失效。可以分普通表和分区表来讨论哪些操作将导致索引失效。

(1)普通表索引失效的情形如下所示:

① 手动置索引无效:ALTER INDEX IND_OBJECT_ID UNUSABLE;。

② 如果对表进行MOVE操作(包含移动表空间和压缩操作)或在线重定义表后,那么该表上所有的索引状态会变为UNUSABLEMOVE操作的SQL语句为:ALTER TABLE TT MOVE;

③ SQL*Loader加载数据。

SQL*Loader加载过程中会维护索引,由于数据量比较大,在SQL*Loader加载过程中出现异常情况,也会导致Oracle来不及维护索引,导致索引处于失效状态,影响查询和加载。异常情况主要有:在加载过程中杀掉SQL*Loader进程、重启或表空间不足等。

(2)分区表索引失效的情形如下所示:

① 对分区表的某个含有数据的分区执行了TRUNCATEDROP操作可以导致该分区表的全局索引失效,而分区索引依然有效,如果操作的分区没有数据,那么不会影响索引的状态。需要注意的是,对分区表的ADD操作对分区索引和全局索引没有影响。

② 执行EXCHANGE操作后,全局索引分区索引都无条件地会被置为UNUSABLE(无论分区是否含有数据)但是,若包含INCLUDING INDEXES子句(缺省情况下为EXCLUDING INDEXES),则全局索引会失效,而分区索引依然有效。

③ 如果执行SPLIT的目标分区含有数据,那么在执行SPLIT操作后,全局索引分区索引都会被被置为UNUSABLE。如果执行SPLIT的目标分区没有数据,那么不会影响索引的状态。

④ 对分区表执行MOVE操作后,全局索引和分区索引都会被置于无效状态。

⑤ 手动置其无效:ALTER INDEX IND_OBJECT_ID UNUSABLE;

对于分区表而言,除了ADD操作之外,TRUNCATEDROPEXCHANGESPLIT操作均会导致全局索引失效,但是可以加上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);

对全局索引和分区索引都影响

 

& 说明:

有关数据库操作导致索引失效的更多内容可以参考我的BLOGhttp://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笔试面宝典》读者群小麦苗的微店

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面试宝典》读者群       小麦苗的微店

.............................................................................................................................................


DBA笔试面试讲解群
《DBA宝典》读者群 欢迎与我联系



请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1066
  • 访问量
    6931503