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

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

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

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

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

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

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

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

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

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

看一個(gè)連接謂詞推入的實(shí)例，創(chuàng)建測試表、相關(guān)索引和一個(gè)普通視圖和一個(gè)帶有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不對(duì)視圖EMP_VIEW做視圖合并，這樣就具備了做連接謂詞推入的基本條件。這里外部查詢和視圖EMP_VIEW的連接條件為“emp.empno=emp_view.empno1(+)”，由于已經(jīng)在視圖EMP_VIEW的基表EMP1的列EMPNO上創(chuàng)建了索引IDX_EMP1，而且這里的連接類型又是外連接，根據(jù)前面的介紹，對(duì)于視圖EMP_VIEW而言，所有能做連接謂詞推入的條件都已具備，Oracle在執(zhí)行上面的SQL時(shí)會(huì)考慮做連接謂詞推入。如果做連接謂詞推入，執(zhí)行計(jì)劃就會(huì) 走嵌套循環(huán)外連接并且訪問視圖EMP_VIEW的基表EMP1時(shí)會(huì)使用列EMPNO上的索引IDX_EMP1。

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

如果不做連接謂詞推入，那Oracle在訪問視圖EMP_VIEW的基表EMP1時(shí)就只能做全表掃描了。在測試SQL中加入no_push_pred hint(讓優(yōu)化器不要對(duì)視圖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ì)劃已經(jīng)變?yōu)榱薍ASH JOIN OUTER，而且對(duì)EMP_VIEW的基表EMP1確實(shí)用的是全表掃描。

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

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

在SQL中加入no_push_pred hint(讓優(yōu)化器不要對(duì)視圖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í)行計(jì)劃可以看出，不使用連接謂詞推入，則對(duì)視圖的基表做的是全表掃描。

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

現(xiàn)在來驗(yàn)證一下，在上面的SQL中加入cardinality hint，讓CBO認(rèn)為外圍查詢的結(jié)果集的Cardinality是1萬，這樣就會(huì)急劇增加做連接謂詞推入后的嵌套循環(huán)連接的成本，如果Oracle在做連接謂詞推入是確實(shí)會(huì)考慮成本，那么此時(shí)Oracle就一定不會(huì)再選擇做連接謂詞推入。

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沒有選擇做連接謂詞推入，此時(shí)的成本為10，使用push_pred強(qiáng)制做連接謂詞推入，看到成本為20008。這也驗(yàn)證了之前說的Oracle在做連接謂詞推入會(huì)考慮成本。

下面再看使用了內(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

對(duì)于上面的SQL，所有能做連接謂詞推入的條件都已具備，從執(zhí)行計(jì)劃中也可以看出Oracle確實(shí)也做了連接謂詞推入。

再回到一開始執(zhí)行的SQL，把外連接改為內(nèi)連接，并在其中加入push_pred hint(讓優(yōu)化器對(duì)視圖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í)行計(jì)劃來看，Oracle沒有做連接謂詞推入，因?yàn)樗粚儆陂_關(guān)提到的那幾種能做連接謂詞推入的情形，即使使用了Hint也不行。

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

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

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

網(wǎng)站名稱：Oracle查詢轉(zhuǎn)換之連接謂詞推入
文章分享：http://www.rwnh.cn/article38/gcgpsp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、營銷型網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站建設(shè)、App設(shè)計(jì)、網(wǎng)頁設(shè)計(jì)公司、網(wǎng)站營銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)