oracle push_subq简析

此hint是针对自查询的hint,其含义是让cbo尽早的把未展开的子查询进行执行;
一般情况下,默认未no_push_subq,即未展开的子查询肯定是在最后一步执行的;
但是如果此子查询能够过滤大量数据,这个时候让cbo提前执行这个未展开的子查询,从而有效的筛选数据哈; 结合no_unnest使用(仅仅作为演示需要);

create table dept_temp
as
select * from dept where rownum=1;

--不展开
select d.dname,e.*
  from dept d, emp e
 where d.deptno = e.deptno
   and exists (select /*+ no_unnest*/ * from dept_temp ff where ff.dname = d.dname);

可以看到,确实filter是放在了最后操作;我们知道,id=2的数据返回量为12,那么最后的过滤操作为12次;
如果提前让dept跟dept_temp那么结果集就直接为1了;

--push_subq
select d.dname,e.*
  from dept d, emp e
 where d.deptno = e.deptno
   and exists (select /*+ no_unnest push_subq*/ * from dept_temp ff where ff.dname = d.dname);
--等价
select /*+ push_subq(@tt)*/ d.dname,e.*
  from dept d, emp e
 where d.deptno = e.deptno
   and exists (select /*+ no_unnest qb_name(tt)*/ * from dept_temp ff where ff.dname = d.dname);

可以发现,此时的dept_temp提前跟dept做了filter操作;
注意id=3的谓词信息,是filter; 并不是跟dept做hash;
–如果走nl更好,因为此时id=2的结果集为1,很小;作为驱动表很合适;

select /*+ use_nl(e)*/ d.dname,e.*
  from dept d, emp e
 where d.deptno = e.deptno
   and exists (select /*+ no_unnest push_subq */ * from dept_temp ff where ff.dname = d.dname);

这里的问题是不能展开的子查询只能作为被驱动表,虽然过滤能力不错;
当子查询的数量大于主表时,走filter这个完全没问题;
当子查询的数量小于主表时,走这个就是大表驱动小表了,虽然有过滤能力,但不是最佳的选择;

如果子查询能展开:
当子查询的数量大于主表时,cbo走nl或者nl半连接没问题;让主表作为驱动表;
当子查询的数量小于主表时,让子查询部分作为驱动表;依然没问题;
说明在子查询能过滤大量数据的场景下,展开比不展开好;

那么push_subq的使用场景就仅仅局限于:
1.子查询无论是exists还是in都无法展开;
而in只在or d.deptno in (select e.deptno from emp e);
这一种场景下无法展开的,所以说push_subq的使用范围很小很小;
2.在上述的前提下,子查询提前跟主表连接能过滤大量数据;

看看展开的执行计划,你会明白了我上面所说的
select d.dname,e.*
from dept d, emp e
where d.deptno = e.deptno
and exists (select * from dept_temp ff where ff.dname = d.dname);