Oracle查询转换（四）连接谓词推入

在前面 Oracle查询转换（二）复杂视图合并 中有一个例子：

SQL> select /*+ no_merge(e) */d.department_name,e.sal_sum from employees_vw e,departments d where e.department_id=d.department_id and department_name='IT';

Execution Plan
----------------------------------------------------------
Plan hash value: 1313696113

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |     1 |    29 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                   |     1 |    29 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL             | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE         | EMPLOYEES_VW      |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    FILTER                       |                   |       |       |            |          |
|   5 |     SORT AGGREGATE              |                   |     1 |     7 |            |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |    70 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPARTMENT_NAME"='IT')
   4 - filter(COUNT(*)>0)
   7 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

可以看到这个执行计划没有使用视图合并，而是使用了连接谓词推入“VIEW PUSHED PREDICATE”，将连接条件推入到了视图EMPLOYEES_VW内部，这样视图就可以用上基表的索引EMP_DEPARTMENT_IX，从而走出基于该索引的嵌套循环连接。如果不做连接谓词推入，则访问视图的基表就只能走全表扫描了。使用NO_PUSH_PRED Hint让优化器不做连接谓词推入看看：

 

SQL> select /*+ no_merge(e) no_push_pred(e) */d.department_name,e.sal_sum from employees_vw e,departments d where e.department_id=d.department_id and department_name='IT';

Execution Plan
----------------------------------------------------------
Plan hash value: 1182627260

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    42 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |              |     1 |    42 |     7  (29)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |     1 |    16 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK   |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |              |    11 |   286 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      | EMPLOYEES_VW |    11 |   286 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |              |    11 |    77 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMPLOYEES    |   107 |   749 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

 

可以看到不做连接谓词推入后，优化器对视图基本做了全表扫描，且从之前的嵌套循环连接变为了排序合并连接。

 

Oracle能否能做连接谓词推入与目标视图的类型、该视图与外部查询之间的连接类型以及连接方法有关。到目前为止，Oracle 11g支持对如下类型的视图做连接谓词推入。
  ● 视图定义SQL语句中包含UNION ALL/UNION的视图
  ● 视图定义SQL语句中包含DISTINCT的视图
  ● 视图定义SQL语句中包含GROUP BY的视图
  ● 和外部查询之间的连接类型是外连接的视图
  ● 和外部查询之间的连接类型是反连接的视图
  ● 和外部查询之间的连接类型是半连接的视图

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2140858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28536251/viewspace-2140858/