从一条分页sql优化看oracle优化器的不足

ORACLE的优化器,在众多dba眼中都是世界上最好的优化器,一直是其他数据库,包括国产数据库追赶的目标,特别是在查询转换上,oracle确实所向无敌。但是本文要介绍的一个小案例对于oracle来说应该算是一个美中不足的地方。

该sql是一条非常简单的分页sql,由于生产sql不便展示,我在自己的测试环境中复现了一下,发现及时最新的23ai优化器存在着这一点点的小不足。

sql文本非常的简单,T1的object_id列是存在索引的。

执行计划和执行消耗:

要优化该sql非常简单,按照之前写过的一篇分页sql优化文章的方法,很快就能把sql优化掉。由于T1的object_id列是存在索引的,所以只需要创建T2表的(OWNER,LAST_DDL_TIME)基本可以实现秒出,具体原因参考之前的文章。http://www.minniebaby.tech/2023/05/04/sql%e4%bc%98%e5%8c%96%e6%a1%88%e4%be%8b%ef%bc%9a%e5%88%86%e9%a1%b5%e6%9f%a5%e8%af%a2%e4%b8%80/

sql优化:

但是虽然优化完了该sql,也发现了oracle优化器的一个不足,即使没有创建索引,该sql也应该利用rownum的stopkey特性,先去扫描T2表并且排序之后再去连接T1表,而不是先连接再排序再stopkey。

我们把索引删除,手动去改写sql:

虽然肯定比不上之前创建索引的方式,但是也比之前好很多,oracle的查询转换居然没考虑到这种情况。

相比之下mysql居然有这方面的考虑,在没有被优化过的情况下,该sql场景mysql选择了最正确的执行计划。

先扫描T2并且排序之后再与T1关联,很好的用上了limit的特点,而不是像oracle一样先关联再去对关联出的大量结果集排序再去做stopkey。

这也给了我们一个启发,就是如果是从mysql迁移到其他数据库的情况,需要考虑到这种sql场景在mysql上可能会跑的不错,但是在其他数据库上会明显变慢,需要做对应的优化。

此条目发表在Oracle, Oralce performance分类目录,贴了标签。将固定链接加入收藏夹。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注