强制使用索引提高执行效率原因分析

sinye56 2024-10-11 17:49 12 浏览 0 评论

有一张表mm_centralpayment_td有800万条数据，有一个状态字段OPSTATUS建有索引IDX16_121501，这个字段取值只有3种：0：未处理；1：处理完成；z:处理出错。表中的数据大部分都是状态1，小部分是状态0，只有极少数(甚至没有)数据是状态z。

现在有一个定时任务，每分钟会去检查是否有出错的记录， SQL内容如下：

select *

from bpsf.mm_centralpayment_td a

where a.opstatus = 'z';

结果这个SQL经常查出来结果为空，但是每次执行还是要8秒，看执行计划可以发现，这个SQL并没有走索引，而是走了全表扫描。

于是我们让SQL强制走索引：

select /*+INDEX(a IDX16_121501)*/

from bpsf.mm_centralpayment_td a

where a.opstatus = 'z'

结果这个SQL只要0.5秒，执行计划如下：

为什么明明走索引会更快的SQL，ORACLE没有自动走索引呢？其实从执行计划的cost可以看出表面的原因：

第1个SQL全表扫描，总的cost为88039，

第2个SQL走索引，总的cost为170674，

这个SQL走索引cost更高，所以ORACLE没有选择走索引，而是选择了全表扫描。

深层次的原因是因为ORACLE不会记录每个值对应的记录数量，但是会记录表的记录数量级和字段取值的数量级，比如上面的表的记录数量为百万级，字段取值数量为个位数，平均下来每个取值对应的记录数量至少会有几十万，所以ORACLE觉得平均来看，全表扫描会更快。

比如上面的SQL中如果条件改为状态等于1，ORACLE还是会选择全表扫描，这时查出全部数据的速度，就会比走索引快得多。当然这个我没有实测，因为大部分的数据状态都为1，查出近800万数据一是时间会很长，二是客户端也会内存溢出。

由上面的分析我们可以知道，1个字段的值重复度很高时(记录多，取值少)，ORACLE就不会选择走相应的索引，但是如果我们知道某个取值的记录很少，就可以通过强制走索引来提高查询的效率。

相关推荐