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的檢視
  ● 和外部查詢之間的連線型別是外連線的檢視
  ● 和外部查詢之間的連線型別是反連線的檢視
  ● 和外部查詢之間的連線型別是半連線的檢視