連接謂詞推入(Join Predicate  Pushdown)是優(yōu)化器處理帶視圖的目標SQL的一種優(yōu)化手段，它是指雖然優(yōu)化器會把該SQL中視圖的定義SQL語句當作一個獨立單元來單獨執(zhí)行，但此時優(yōu)化器會把原本處于該視圖外部查詢中和該視圖之間的連接條件推入到該視圖的定義SQL語句內(nèi)部，這樣是為了能使用上該視圖內(nèi)部相關基表上的索引，進而能走出基于索引的嵌套循環(huán)連接。

10多年創(chuàng)新互聯(lián)建站網(wǎng)站建設,由一走到現(xiàn)在，當中離不開團隊頑強的創(chuàng)業(yè)精神，離不開伴隨我們同行的客戶與專業(yè)的合作伙伴，創(chuàng)力信息一直秉承以“見一個客戶，了解一個行業(yè)，交一個朋友”的方式為經(jīng)營理念，提出“讓每一個客戶成為我們的終身客戶”為目標，以為用戶提供精細化服務，全面滿足用戶需求為宗旨，誠信經(jīng)營，更大限度為用戶創(chuàng)造價值。期待邁向下一個更好的10多年。

連接謂詞推入所帶來的基于索引的嵌套循環(huán)連接并不一定能走出更高效的執(zhí)行計劃，因為當做了連接謂詞推入后，原目標SQL中的視圖就和外部查詢產(chǎn)生了關聯(lián)，同時Oracle又必須將該視圖的定義SQL語句當作一個獨立的處理單元單獨執(zhí)行，這也就意味著對于外部查詢所在結果集中的每一條記錄，上述視圖的定義SQL語句都得單獨執(zhí)行一次，這樣一旦外部查詢所在的結果集的Cardinality比較大的話，即便在執(zhí)行上述視圖的定義語句時能用上索引，整個SQL的執(zhí)行效率也不定比不做連接謂詞推入時的哈希連接或排序合并連接高。所以Oracle在做連接謂詞推入時會考慮成本，只有當經(jīng)過連接謂詞推入后走嵌套循環(huán)連接的等價改寫SQL的成本值小于原SQL的成本值時，Oracle才會對目標SQL做連接謂詞推入。

Oracle是否能做連接謂詞推入與目標視圖的類型、該視圖與外部查詢之間的連接類型以及連接方法有關。到目前為止，Oracle僅僅支持對如下類型的視圖做連接謂詞推入。

• 視圖定義SQL語句中包含UNION ALL/UNION的視圖

• 視圖定義SQL語句中包含DISTINCT的視圖

• 視圖定義SQL語句中包含GROUP BY的視圖

• 和外部查詢之間的連接類型是外連接的視圖

• 和外部查詢之間的連接類型是反連接的視圖

• 和外部查詢之間的連接類型是半連接的視圖

看一個連接謂詞推入的實例，創(chuàng)建測試表、相關索引和一個普通視圖和一個帶有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.

執(zhí)行測試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(+)”，由于已經(jīng)在視圖EMP_VIEW的基表EMP1的列EMPNO上創(chuàng)建了索引IDX_EMP1，而且這里的連接類型又是外連接，根據(jù)前面的介紹，對于視圖EMP_VIEW而言，所有能做連接謂詞推入的條件都已具備，Oracle在執(zhí)行上面的SQL時會考慮做連接謂詞推入。如果做連接謂詞推入，執(zhí)行計劃就會 走嵌套循環(huán)外連接并且訪問視圖EMP_VIEW的基表EMP1時會使用列EMPNO上的索引IDX_EMP1。

從執(zhí)行計劃上可以看出，Oracle在執(zhí)行測試SQL時確實走的是嵌套循環(huán)外連接，并且訪問視圖EMP_VIEW的基表EMP1時用到了索引IDX_EMP1。而且Id=3的執(zhí)行步驟上Name列的值是“EMP_VIEW”，Operation列的值是“VIEW PUSHED PREDICATE”。這說明Oracle確實沒有對視圖EMP_VIEW做視圖合并，而是把它當作一個獨立的執(zhí)行單元來單獨執(zhí)行，并且把外部查詢和視圖EMP_VIEW之間的連接條件“emp.empno=emp_view.empno1(+)”推入到了視圖的定義語句內(nèi)部。

如果不做連接謂詞推入，那Oracle在訪問視圖EMP_VIEW的基表EMP1時就只能做全表掃描了。在測試SQL中加入no_push_pred hint(讓優(yōu)化器不要對視圖EMP_VIEW做連接謂詞推入)再次執(zhí)行

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

執(zhí)行計劃已經(jīng)變?yōu)榱薍ASH JOIN OUTER，而且對EMP_VIEW的基表EMP1確實用的是全表掃描。

現(xiàn)在把測試SQL改一下，把EMP_VIEW用EMP_VIEW_UNION視圖替換，并把連接類型改為內(nèi)連接，再次執(zhí)行

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列都有索引，雖然這里的連接類型是內(nèi)連接，但對于包含UNION ALL的視圖EMP_VIEW_UNION而言，所有能作連接謂詞推入的條件都已具備，意味著Oracle地執(zhí)行上述SQL時做考慮做連接謂詞推入。如果做連接謂詞推入，那執(zhí)行計劃就會走嵌套循環(huán)連接，并且訪問視圖的基表會用上列EMPNO上的索引。

從執(zhí)行計劃中可以看出，Oracle走的執(zhí)行計劃與預想的一樣。

在SQL中加入no_push_pred hint(讓優(yōu)化器不要對視圖EMP_VIEW做連接謂詞推入)再次執(zhí)行

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

從執(zhí)行計劃可以看出，不使用連接謂詞推入，則對視圖的基表做的是全表掃描。

之前提到過，Oracle在做連接謂詞推入時會考慮成本，只有經(jīng)過連接謂詞推入后走嵌套循環(huán)連接的等價改寫SQL的成本值小于原SQL的成本值時，Oracle才會對目標SQL做連接謂詞推入。

現(xiàn)在來驗證一下，在上面的SQL中加入cardinality hint，讓CBO認為外圍查詢的結果集的Cardinality是1萬，這樣就會急劇增加做連接謂詞推入后的嵌套循環(huán)連接的成本，如果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在做連接謂詞推入會考慮成本。

下面再看使用了內(nèi)嵌視圖且連接類型為外連接的示例：

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，所有能做連接謂詞推入的條件都已具備，從執(zhí)行計劃中也可以看出Oracle確實也做了連接謂詞推入。

再回到一開始執(zhí)行的SQL，把外連接改為內(nèi)連接，并在其中加入push_pred hint(讓優(yōu)化器對視圖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

從執(zhí)行計劃來看，Oracle沒有做連接謂詞推入，因為它不屬于開關提到的那幾種能做連接謂詞推入的情形，即使使用了Hint也不行。

雖然Oracle是否能做連接謂詞推入與目標視圖是否能做視圖合并、是否是內(nèi)嵌視圖沒有關系，但是與目標視圖的類型、與外查詢之間的連接類型及連接方法是有關系的。到目前為止，Oracle里能做連接謂詞推入的情形公限于開頭提到的那幾種類型，如果不屬于這些情形，即便是看起來很簡單，Oracle也不會做。

參考《基于Oracle的SQL優(yōu)化》

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