警惕 Oracle 索引优化时陷阱之无效的索引范围扫描(INDEX RANGE SCAN)导致的全表扫描

怼烎@ 2023-02-23 08:50 22阅读 0赞

生产环境慢查询统计中，发现表 STATUS 的 MILESTONE 字段条件查询时进行了全表扫描。表 STATUS 的 MILESTONE 字段定义如下：  
![表 STATUS 的 MILESTONE 字段定义.png][STATUS _ MILESTONE _.png]  
针对上述问题创建索引：

create index STATUS_MILESTONE on STATUS("MILESTONE") tablespace DFS_INDEX2;

分析执行计划发现问题语句依然走的是全表扫描，新建的索引 STATUS\_MILESTONE 没有用到。第一反应是索引是不是没有创建成功？  
查看索引库发现该索引确实存在并且生效：  
![查看索引库发现该索引确实存在.png][.png]  
查看 STATUS 表 DDL 也可以看到该索引的定义：  
![查看 STATUS 表 DDL 也可以看到该索引的定义.png][STATUS _ DDL _.png]  
看来我们的索引被 Oracle 忽视了。Oracle 无视现有索引有很多种原因，但一般来讲有这两种原因：

*  该 sql 的写法导致 Oracle 无法使用已有索引(最左前缀原则，请参考博客[《B-Tree 索引和 Hash 索引的对比》][B-Tree _ Hash])。
 *  Oracle 基于成本的优化器选择不使用该索引。

测试库中 STATUS 表记录数：  
![测试库中 STATUS 表记录数.png][STATUS _.png]  
观察 STATUS 表 MILESTONE 字段相关数据特点，发现该字段的记录只有少数不同值的列：  
![该字段的记录只有少数不同值的列.png][.png 1]  
现在我们以简单查询语句 select \* from status S WHERE S.MILESTONE = ‘IFFDEL’ 来验证执行计划：  
![简单查询语句来验证执行计划.png][.png 2]  
全表扫，cost 为 25。  
强制优化器走索引 STATUS\_MILESTONE 并分析其执行计划：  
![强制优化器走索引 STATUS\_MILESTONE 并分析其执行计划.png][STATUS_MILESTONE _.png]  
索引范围扫描，cost 61，比全表扫还昂贵。很明显本文描述问题命中上述不走索引的两大主流原因之“Oracle 基于成本的优化器选择不使用该索引”的情况。  
如果创建索引时不进行显式说明，Oracle 默认创建的都是 B-tree 索引。也就是说上文中我们创建的索引 STATUS\_MILESTONE 是一个 B-tree 索引。  
B-tree 的优势在于大量不同的记录，而位图索引的优势在于相对于表中行总数而言只有少数不同值的列，所以本文 select \* from status S WHERE S.MILESTONE = ‘IFFDEL’ 示例用位图索引效果会更好。重新创建索引如下：

drop index STATUS_MILESTONE;
    create bitmap index STATUS_MILESTONE_BITMAP on STATUS("MILESTONE") tablespace DFS_INDEX2;

当然，在多字段检索复杂查询中，可能还是联合 B-tree 索引优势更明显。  
总结：  
造成无效的范围索引扫描有很多原因，除了本文中的少数不同值的情况之外，还可能存在以下可能：

*  使用了范围谓词，如 <、>、LIKE 和 BETWEEN。
 *  只使用到了组合索引的部分列。

# 参考资料 #

*  [The index is being ignored][]
 *  [Index Range Scans][]

[STATUS _ MILESTONE _.png]: https://img-blog.csdnimg.cn/20200706202537730.png
[.png]: https://img-blog.csdnimg.cn/20200706202631347.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlZm9uZHM=,size_16,color_FFFFFF,t_70
[STATUS _ DDL _.png]: https://img-blog.csdnimg.cn/20200706202643709.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlZm9uZHM=,size_16,color_FFFFFF,t_70
[B-Tree _ Hash]: https://blog.csdn.net/defonds/article/details/46779169
[STATUS _.png]: https://img-blog.csdnimg.cn/20200706202715353.png
[.png 1]: https://img-blog.csdnimg.cn/20200706202727483.png
[.png 2]: https://img-blog.csdnimg.cn/20200706202737995.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlZm9uZHM=,size_16,color_FFFFFF,t_70
[STATUS_MILESTONE _.png]: https://img-blog.csdnimg.cn/20200706202749331.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlZm9uZHM=,size_16,color_FFFFFF,t_70
[The index is being ignored]: http://www.orafaq.com/tuningguide/not%20using%20index.html
[Index Range Scans]: http://www.orafaq.com/tuningguide/range%20scan.html