107-改进写法,让SQL执行效率更上一层楼

有下面这样一种SQL写法,关联条件里面有or:

其中 demo101_t1(以下简称t1)和demo101_t2(以下简称t2)都是大表(几千万以上记录), 两表关联字段上重复值都比较少,如果t2表上不创建合适的索引, 这个SQL的执行效率将会是极差的(t2表做几千万次的全表扫描,估计要执行几天吧),执行计划是这样的:

如果在t2表上分别创建object_id和object_name两个单字段索引, 效率会提升很多,但是一个大结果集做filter的驱动表, 效率仍不能让人满意(可能很多人到这里就会接受当前这个执行效率了), 这个执行间可能仍要以小时计, 执行计划会是这样的:

有优化专家对这个SQL做了改写, 改写后的sql如下:

对应的执行计划如下:

老虎刘点评:

将关联的两部分用union连接, 再把可能重复的记录用rowid去重,这个改写挺巧妙, 也不需要索引的配合. 美中不足的是, 两个大结果集做union,这个消耗也挺大(上图步骤3).

一般的or改写, 都是建议使用union all, 但是很多人怕使用union all不好处理重复记录(有些记录可能会同时满足union all的上下两部分的条件,产生重复), 要做到等价需要再加点条件;

而如果是用union, 如果不使用rowid或主键,对于本来就可能需要正常返回的重复记录, 做了去重处理, 还会丢记录, 效率比union all要低, 而且还造成了不等价.

老虎刘的改写尝试:

根据oracle的一贯做法,or的改写是 union all配合lnnvl:

写法等价, 但是很遗憾, oracle的优化器在这里好像脑子短路了, 这种写法得到的执行计划,不是我们想要的,union all的下半部分执行计划,变回了filter,我们想要的是跟上面一样也是Hash Join Semi:

(注:如果关联字段上没有null值, lnnvl部分可以直接替换成a.object_name<>b.object_name, 这个写法就能得到我们想要的执行计划,不会出现下面这种执行计划. 我的改写是为了保证方法的通用性, 关联字段上有null记录也适用, 不考虑null记录可能就不等价了)

我把lnnvl函数做了等价改写, 让postgresql(12.15)和mysql(8.0+)也能执行这个SQL,这两种库都能得到想要的执行计划, 而oracle还是只能得到union all下半部分是filter的低效执行计划(上图):

为了让oracle能够得到下半部分也是Hash Join Semi的执行计划, 我做了一些尝试, 用下面写法, 能得到我们想要的完美执行计划 :

我们最终想要的执行计划是下面这样的:

(在oracle 数据库的11.2.0.3和19.17分别测试, 结果是一样的).

总结:

SQL优化有规律可循, 如果优化器实现不了我们想要的, 我们就用顺着优化器能做到的方向走, 直到实现我们的最终优化目标.

(完)