scott@TEST>select emp.empno
2 from emp,emp_view_union
3 where emp.empno=emp_view_union.empno1
4 and emp.ename='FORD';
EMPNO
----------
7902
7902
视图EMP_VIEW_UNION的定义SQL语句中包含UNION ALL,它本身就不能做视图合并,因而具备了做连接谓词推入的基本条件。这里外部查询和视图EMP_VIEW_UNION的连接条件为“emp.empno=emp_view_union.empno1”视图对基表上的EMPNO列都有索引,虽然这里的连接类型是内连接,但对于包含UNION ALL的视图EMP_VIEW_UNION而言,所有能作连接谓词推入的条件都已具备,意味着Oracle地执行上述SQL时做考虑做连接谓词推入。如果做连接谓词推入,那执行计划就会走嵌套循环连接,并且访问视图的基表会用上列EMPNO上的索引。
从执行计划中可以看出,Oracle走的执行计划与预想的一样。
在SQL中加入no_push_pred hint(让优化器不要对视图EMP_VIEW做连接谓词推入)再次执行
scott@TEST>select /*+ no_push_pred(emp_view_union) */emp.empno
2 from emp,emp_view_union
3 where emp.empno=emp_view_union.empno1
4 and emp.ename='FORD';
EMPNO
----------
7902
7902
从执行计划可以看出,不使用连接谓词推入,则对视图的基表做的是全表扫描。
之前提到过,Oracle在做连接谓词推入时会考虑成本,只有经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时,Oracle才会对目标SQL做连接谓词推入。
现在来验证一下,在上面的SQL中加入cardinality hint,让CBO认为外围查询的结果集的Cardinality是1万,这样就会急剧增加做连接谓词推入后的嵌套循环连接的成本,如果Oracle在做连接谓词推入是确实会考虑成本,那么此时Oracle就一定不会再选择做连接谓词推入。
scott@TEST>select /*+ cardinality(emp 10000) */emp.empno
2 from emp,emp_view_union
3 where emp.empno=emp_view_union.empno1
4 and emp.ename='FORD';
EMPNO
----------
7902
7902
scott@TEST>select /*+ cardinality(emp 10000) push_pred(emp_view_union) */emp.empno
2 from emp,emp_view_union
3 where emp.empno=emp_view_union.empno1
4 and emp.ename='FORD';
EMPNO
----------
7902
7902
从上面的测试可以看出使用cardinality hint后Oracle没有选择做连接谓词推入,此时的成本为10,使用push_pred强制做连接谓词推入,看到成本为20008。这也验证了之前说的Oracle在做连接谓词推入会考虑成本。
下面再看使用了内嵌视图且连接类型为外连接的示例:
scott@TEST>select /*+ no_merge(emp_view_inline) */ emp.empno
2 from emp,(select emp1.empno as empno1 from emp1) emp_view_inline
3 where emp.empno=emp_view_inline.empno1(+)
4 and emp.ename='FORD';
EMPNO
----------
7902
对于上面的SQL,所有能做连接谓词推入的条件都已具备,从执行计划中也可以看出Oracle确实也做了连接谓词推入。
再回到一开始执行的SQL,把外连接改为内连接,并在其中加入push_pred hint(让优化器对视图EMP_VIEW做连接谓词推入)和USE_NL hint
scott@TEST>select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */ emp.empno
2 from emp,emp_view
3 where emp.empno=emp_view.empno1
4 and emp.ename='FORD';
EMPNO
----------
7902
从执行计划来看,Oracle没有做连接谓词推入,因为它不属于开关提到的那几种能做连接谓词推入的情形,即使使用了Hint也不行。