Oracle查詢轉換之連接謂詞推入

連接謂詞推入(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優化》

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