Oracle查询转换之连接谓词推入

共计 4851 个字符，预计需要花费 13 分钟才能阅读完成。

连接谓词推入 (Join Predicate  Pushdown) 是优化器处理带视图的目标 SQL 的一种优化手段，它是指虽然优化器会把该 SQL 中视图的定义 SQL 语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义 SQL 语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。

连接谓词推入所带来的基于索引的嵌套循环连接并不一定能走出更高效的执行计划，因为当做了连接谓词推入后，原目标 SQL 中的视图就和外部查询产生了关联，同时 Oracle 又必须将该视图的定义 SQL 语句当作一个独立的处理单元单独执行，这也就意味着对于外部查询所在结果集中的每一条记录，上述视图的定义 SQL 语句都得单独执行一次，这样一旦外部查询所在的结果集的 Cardinality 比较大的话，即便在执行上述视图的定义语句时能用上索引，整个 SQL 的执行效率也不定比不做连接谓词推入时的哈希连接或排序合并连接高。所以 Oracle 在做连接谓词推入时会考虑成本，只有当经过连接谓词推入后走嵌套循环连接的等价改写 SQL 的成本值小于原 SQL 的成本值时，Oracle 才会对目标 SQL 做连接谓词推入。

Oracle 是否能做连接谓词推入与目标视图的类型、该视图与外部查询之间的连接类型以及连接方法有关。到目前为止，Oracle 仅仅支持对如下类型的视图做连接谓词推入。

视图定义 SQL 语句中包含 UNION ALL/UNION 的视图

视图定义 SQL 语句中包含 DISTINCT 的视图

视图定义 SQL 语句中包含 GROUP BY 的视图

和外部查询之间的连接类型是外连接的视图

和外部查询之间的连接类型是反连接的视图

和外部查询之间的连接类型是半连接的视图

看一个连接谓词推入的实例，创建测试表、相关索引和一个普通视图和一个带有 UNION ALL 的视图

scott@TEST>create table emp1 as select * from emp;
 
Table created.
 
scott@TEST>create table emp2 as select * from emp;
 
Table created.
 
scott@TEST>create index idx_emp1 on emp1(empno);
 
Index created.
 
scott@TEST>create index idx_emp2 on emp2(empno);
 
Index created.
 
scott@TEST>create or replace view emp_view as
  2  select emp1.empno as empno1 from emp1;
 
View created.
 
scott@TEST>create or replace view emp_view_union as
  2  select emp1.empno as empno1 from emp1
  3  union all
  4  select emp2.empno as empno1 from emp2;
 
View created.

执行测试 SQL

 scott@TEST>select /*+ no_merge(emp_view) */ emp.empno
  2  from emp,emp_view
  3  where emp.empno=emp_view.empno1(+)
  4  and emp.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
  2  from emp,emp_view
  3  where emp.empno=emp_view.empno1(+)
  4  and emp.ename=’FORD’;
 
    EMPNO
———-
      7902

执行计划已经变为了 HASH JOIN OUTER，而且对 EMP_VIEW 的基表 EMP1 确实用的是全表扫描。

现在把测试 SQL 改一下，把 EMP_VIEW 用 EMP_VIEW_UNION 视图替换，并把连接类型改为内连接，再次执行

 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 也不行。

虽然 Oracle 是否能做连接谓词推入与目标视图是否能做视图合并、是否是内嵌视图没有关系，但是与目标视图的类型、与外查询之间的连接类型及连接方法是有关系的。到目前为止，Oracle 里能做连接谓词推入的情形公限于开头提到的那几种类型，如果不属于这些情形，即便是看起来很简单，Oracle 也不会做。

参考《基于 Oracle 的 SQL 优化》PDF 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#i55050

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141715.htm