今天有个学员在微信群里问了一个问题 : 书上说exists 子查询带union只能使用filter的执行计划, 但是他实验模拟得到的执行计划却是hash semi join,为什么?
下面是书上写的(图1):
下面是学员实验得出的(图2):
请注意我在图1红框中标注的两个emp.的位置, 与图2中E.deptno位置的区别.
(与是否使用别名无关)
为什么书上的写法与实验结果对不上呢? 对此我做了分析与模拟, 得到了下面的结论:
如果把图2 SQL其中一个E.deptno放到=的后面(即E.DEPTNO=D.DEPTNO改成D.DEPTNO=E.DEPTNO), 那么也只能得到图1的执行计划. 加hint也不行(至少我没有尝试成功), 19c版本也是一样的.
但是如果union各部分关联条件的写法顺序是一致的, 比如都是E.DEPTNO在前或都是E.DEPTNO在后, 还是可以得出不使用filter的执行计划(unnest的效果).
一个简单的等值关联条件的前后顺序调换, 就对执行计划产生如此严重的影响,说明oracle的优化器还是有一定的不足. 另外, 书上如果用的是图2写法的SQL, 也不会得出exists+union只能使用filter执行计划的片面说法.
另外, SQL的写法也很重要, 这种exists 子查询带union/union all的写法, 一般都会使用in的写法, 这样就不会出现上面的情况了.
大家有时间可以自己动手, 比较一下下面两个sql的执行计划:
--只能使用filter
select ename, deptno
from emp
where exists
(
select deptno from dept where dname = 'CHICAGO' and emp.deptno =dept.deptno
union
select deptno from dept where loc = 'CHICAGO' and dept.deptno = emp.deptno
);
--可以不使用filter:
select ename, deptno
from emp e
where exists
(
select deptno from dept d where d.dname = 'CHICAGO' and e.deptno =d.deptno
union
select deptno from dept d where d.loc = 'CHICAGO' and e.deptno = d.deptno
);
本文分享自 老虎刘谈oracle性能优化 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!