96 - or exists写法分析与优化方法

偶然看到一个国产数据库的SQL优化介绍:

下面分析一下这两种写法的优劣:

原or exists写法(写法1):

如果test表结果集小(不含or条件), 那么最终返回的结果集也小,如果test_bak表的object_id字段上有索引, 这种情况是不需要改写的. 如果test_bak表比较大, 改写后反而性能会变差(可能没有merge和push_pred这些查询转换, 大表test_bak要先全表扫描去重, 这个消耗是比较大的).

上面left join改写(写法2)比较适合的场景是:

test表结果集大, test_bak结果集大, 最终结果集也大的情况, 两表可以做hash join, 避免主表做大量filter操作导致性能很差.

上面的改写还漏掉了一个比较重要而且常见的情况, 那就是test表结果集大, 最终结果集小的场景, 这个场景在OLTP系统也是比较常见的, 这种情况改成union all是最佳的(写法3):

select * from test where owner='SCOTT'

union all

select * from test where exists

(select 1 from test_bak where test.object_id=test_bak.object_id)

and lnnvl(owner='SCOTT');

需要特别注意的是: 是用union all改写, 不是union.

回到oracle数据库, 在版本12.2 前, 也要根据上面规则做对应的改写;

在12.2及以上版本,如果写法1效率差, 而且数据分布符合写法3 , 可以不需要改写, 而是通过or_expand的hint让优化器根据指示, 做出查询转换变成写法3; 如果数据分布符合写法2, 还是需要手动改写.

OLTP系统返回一般返回的结果集小, 写法1和写法3 总有一个是适用的 , 而且hint是可以应用到某个具体SQL的. 所以在12.2之后, 不太复杂的 or exists 基本上就不存在性能问题了. 如果是复杂的or exists , 那就要根据情况见招拆招了.

文中观点仅代表本人, 如有不妥, 请指正, 感谢!

全文完