Oracle查询转换之连接谓词推入
|
连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的一种优化手段，它是指虽然优化器会把该SQL中视图的定义SQL语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。 连接谓词推入所带来的基于索引的嵌套循环连接并不一定能走出更高效的执行计划，因为当做了连接谓词推入后，原目标SQL中的视图就和外部查询产生了关联，同时Oracle又必须将该视图的定义SQL语句当作一个独立的处理单元单独执行，这也就意味着对于外部查询所在结果集中的每一条记录，上述视图的定义SQL语句都得单独执行一次，这样一旦外部查询所在的结果集的Cardinality比较大的话，即便在执行上述视图的定义语句时能用上索引，整个SQL的执行效率也不定比不做连接谓词推入时的哈希连接或排序合并连接高。所以Oracle在做连接谓词推入时会考虑成本，只有当经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL做连接谓词推入。 Oracle是否能做连接谓词推入与目标视图的类型、该视图与外部查询之间的连接类型以及连接方法有关。到目前为止，Oracle仅仅支持对如下类型的视图做连接谓词推入。
看一个连接谓词推入的实例，创建测试表、相关索引和一个普通视图和一个带有UNION ALL的视图 scott@TEST>createtableemp1asselect*fromemp; Tablecreated. scott@TEST>createtableemp2asselect*fromemp; Tablecreated. scott@TEST>createindexidx_emp1onemp1(empno); Indexcreated. scott@TEST>createindexidx_emp2onemp2(empno); Indexcreated. scott@TEST>createorreplaceviewemp_viewas 2selectemp1.empnoasempno1fromemp1; Viewcreated. scott@TEST>createorreplaceviewemp_view_unionas 2selectemp1.empnoasempno1fromemp1 3unionall 4selectemp2.empnoasempno1fromemp2; Viewcreated. 执行测试SQL scott@TEST>select/*+no_merge(emp_view)*/emp.empno 2fromemp,emp_view 3whereemp.empno=emp_view.empno1(+) 4andemp.ename='FORD'; EMPNO ---------- 7902 在上面的SQL中，我们使用了no_merge hint是为了让Oracle不对视图EMP_VIEW做视图合并，这样就具备了做连接谓词推入的基本条件。这里外部查询和视图EMP_VIEW的连接条件为“emp.empno=emp_view.empno1(+)”，由于已经在视图EMP_VIEW的基表EMP1的列EMPNO上创建了索引IDX_EMP1，而且这里的连接类型又是外连接，根据前面的介绍，对于视图EMP_VIEW而言，所有能做连接谓词推入的条件都已具备，Oracle在执行上面的SQL时会考虑做连接谓词推入。如果做连接谓词推入，执行计划就会 走嵌套循环外连接并且访问视图EMP_VIEW的基表EMP1时会使用列EMPNO上的索引IDX_EMP1。
从执行计划上可以看出，Oracle在执行测试SQL时确实走的是嵌套循环外连接，并且访问视图EMP_VIEW的基表EMP1时用到了索引IDX_EMP1。而且Id=3的执行步骤上Name列的值是“EMP_VIEW”，Operation列的值是“VIEW PUSHED PREDICATE”。这说明Oracle确实没有对视图EMP_VIEW做视图合并，而是把它当作一个独立的执行单元来单独执行，并且把外部查询和视图EMP_VIEW之间的连接条件“emp.empno=emp_view.empno1(+)”推入到了视图的定义语句内部。 如果不做连接谓词推入，那Oracle在访问视图EMP_VIEW的基表EMP1时就只能做全表扫描了。在测试SQL中加入no_push_pred hint(让优化器不要对视图EMP_VIEW做连接谓词推入)再次执行 scott@TEST>select/*+no_merge(emp_view)no_push_pred(emp_view)*/emp.empno 2fromemp,emp_view 3whereemp.empno=emp_view.empno1(+) 4andemp.ename='FORD'; EMPNO ---------- 7902
现在把测试SQL改一下，把EMP_VIEW用EMP_VIEW_UNION视图替换，并把连接类型改为内连接，再次执行 scott@TEST>selectemp.empno 2fromemp,emp_view_union 3whereemp.empno=emp_view_union.empno1 4andemp.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上的索引。
在SQL中加入no_push_pred hint(让优化器不要对视图EMP_VIEW做连接谓词推入)再次执行 scott@TEST>select/*+no_push_pred(emp_view_union)*/emp.empno 2fromemp,emp_view_union 3whereemp.empno=emp_view_union.empno1 4andemp.ename='FORD'; EMPNO ---------- 7902 7902
之前提到过，Oracle在做连接谓词推入时会考虑成本，只有经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL做连接谓词推入。 现在来验证一下，在上面的SQL中加入cardinality hint，让CBO认为外围查询的结果集的Cardinality是1万，这样就会急剧增加做连接谓词推入后的嵌套循环连接的成本，如果Oracle在做连接谓词推入是确实会考虑成本，那么此时Oracle就一定不会再选择做连接谓词推入。 scott@TEST>select/*+cardinality(emp10000)*/emp.empno 2fromemp,emp_view_union 3whereemp.empno=emp_view_union.empno1 4andemp.ename='FORD'; EMPNO ---------- 7902 7902
scott@TEST>select/*+cardinality(emp10000)push_pred(emp_view_union)*/emp.empno 2fromemp,emp_view_union 3whereemp.empno=emp_view_union.empno1 4andemp.ename='FORD'; EMPNO ---------- 7902 7902
下面再看使用了内嵌视图且连接类型为外连接的示例： scott@TEST>select/*+no_merge(emp_view_inline)*/emp.empno 2fromemp,(selectemp1.empnoasempno1fromemp1)emp_view_inline 3whereemp.empno=emp_view_inline.empno1(+) 4andemp.ename='FORD'; EMPNO ---------- 7902
再回到一开始执行的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 2fromemp,emp_view 3whereemp.empno=emp_view.empno1 4andemp.ename='FORD'; EMPNO ---------- 7902
从执行计划来看，Oracle没有做连接谓词推入，因为它不属于开关提到的那几种能做连接谓词推入的情形，即使使用了Hint也不行。 虽然Oracle是否能做连接谓词推入与目标视图是否能做视图合并、是否是内嵌视图没有关系，但是与目标视图的类型、与外查询之间的连接类型及连接方法是有关系的。到目前为止，Oracle里能做连接谓词推入的情形公限于开头提到的那几种类型，如果不属于这些情形，即便是看起来很简单，Oracle也不会做。 参考《基于Oracle的SQL优化》 官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#i55050 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
