人在江湖漂，哪能不挨刀之ORACLE分区表(下)

字数 1916阅读 4256评论 0赞 0

作者：韩涛
文章来自微信公众号：平台人生

上次的分享不知道大家是不是还记得，在本地索引中某个分区失效的极端情况下，如果分区键的值是通过一个子查询结果来获取的，优化器将不能确定分区扫描的范围，并将认为相关本地索引为不可用状态，导致执行计划改变的严重后果。今天我们就继续分析下这个案例，看看强壮的本地索引到底存在着哪些软肋呢？

一、追查元凶

既然问题的根本是本地索引IND_LOCAL，首先想到的就是查看下表和本地索引各个分区的last_ddl_time时间。主要观察T_PART表和T_RANGE_P3分区的last_ddl_time时间。从结果可以看出在20161115 03:15:17，对T_RANGE_P3有过DDL操作，导致了本地索引失效。那么究竟是什么操作呢？按照问题时间，通过查询ASH视图，成功定位到了造成本地索引失效的语句。即当晚对T_RANGE_P3分区进行了交换分区操作，导致了本地索引失效。此处强烈推荐设置一个ORACLE参数：enable_ddl_logging，该参功能为记录数据库中所有的DDL操作，该参数默认值为false，在线修改生效，设置为true后，可以根据时间点直接去alert日志中查找对应DDL语句，更加方便准确快捷。

图一对象last_ddl_time时间

图二 DDL语句及执行时间

二、见招拆招

交换分区作为迅速导入分区数据的一种方式，难道因为这个就不能使用了吗？当然不是，查看文档后发现后面还可以加入update indexes和including indexes参数来维护索引，但到底效果如何，各位还是眼见为实吧。

无参数
在不添加任何参数的情况下，全局索引和本地索引对应分区状态均为UNUSABLE，其余无影响。

图三无参数索引状态
添加update indexes参数
在添加update indexes参数的情况下，本地索引对应分区状态均为UNUSABLE，其余无影响。

图四update indexes参数索引状态
添加update global indexes参数
在添加update global indexes参数的情况下，本地索引对应分区状态均为UNUSABLE，其余无影响。

图五update global indexes参数索引状态
添加including indexes参数
添加including indexes参数，分区表(T_PART)和交换表(T3)，必须具备如下条件
1) 如果分区表中含有主键，交换表中相应字段也必须存在主键；
2) 如果分区表中存在本地索引，交换表中相应字段也必须存在索引，索引顺序必须相同，名称可以不同；
3) 如果交换表中存在索引，分区表中相应字段也必须存在本地索引，索引顺序必须相同，名称可以不同；
4) 如果分区表中存在全局索引，交换表中相应字段必须不存在索引；
在添加including indexes参数的情况下，全局索引状态为UNUSABLE，其余无影响。

图六including indexes参数索引状态
添加including indexesupdate indexes
在满足4中条件下，添加including indexesupdate indexes参数，所有索引均有效


图七including indexesupdate indexes参数索引状态
添加including indexesupdateglobal indexes
在满足4中条件下，添加including indexesupdateglobal indexes参数，所有索引均有效。

图八including indexesupdate global indexes参数索引状态
综合以上情况，可以得出下表

表一exchange中参数对索引影响

三、举一反三

其他常用分区操作
之前认为分区表的本地索引和普通的B Tree索引一样强壮，除非人工置为UNUSABLE状态，否则都不会失效，看来又是经验主义了。既然交换分区操作，对分区索引影响这么大，那么分区的其他操作，会不会也有如此大的影响呢？为了验证，我们先来梳理一下常用的分区操作都有哪些。
分区操作影响大集合
1) 分区的各种操作，在不添加任何参数的情况下，对索引的影响见下表

表二分区操作不添加参数下对索引影响
2) 以上操作(除EXCHANGE外)添加update indexes参数，本地、全局索引均不会失效；
添加update global indexes参数，全局索引不会失效，受影响的本地索引会失效。
3) 以上操作(除EXCHANGE外)如果是对空分区做操作，本地、全局索引均不会失效。