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的視圖

<code>scott@TEST></code><code>create</code> <code>table</code> <code>emp1 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>emp;</code>

<code>Table</code> <code>created.</code>

<code>scott@TEST></code><code>create</code> <code>table</code> <code>emp2 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>emp;</code>

<code>scott@TEST></code><code>create</code> <code>index</code> <code>idx_emp1 </code><code>on</code> <code>emp1(empno);</code>

<code>Index</code> <code>created.</code>

<code>scott@TEST></code><code>create</code> <code>index</code> <code>idx_emp2 </code><code>on</code> <code>emp2(empno);</code>

<code>scott@TEST></code><code>create</code> <code>or</code> <code>replace</code> <code>view</code> <code>emp_view </code><code>as</code>

<code> </code><code>2 </code><code>select</code> <code>emp1.empno </code><code>as</code> <code>empno1 </code><code>from</code> <code>emp1;</code>

<code>View</code> <code>created.</code>

<code>scott@TEST></code><code>create</code> <code>or</code> <code>replace</code> <code>view</code> <code>emp_view_union </code><code>as</code>

<code> </code><code>2 </code><code>select</code> <code>emp1.empno </code><code>as</code> <code>empno1 </code><code>from</code> <code>emp1</code>

<code> </code><code>3 </code><code>union</code> <code>all</code>

<code> </code><code>4 </code><code>select</code> <code>emp2.empno </code><code>as</code> <code>empno1 </code><code>from</code> <code>emp2;</code>

執行測試SQL

<code>scott@TEST></code><code>select</code> <code>/*+ no_merge(emp_view) */ emp.empno</code>

<code> </code><code>3 </code><code>where</code> <code>emp.empno=emp_view.empno1(+)</code>

<code> </code><code>4 </code><code>and</code> <code>emp.ename=</code><code>'FORD'</code><code>;</code>

<code> </code><code>EMPNO</code>

在上面的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做連接配接謂詞推入)再次執行

<code>scott@TEST></code><code>select</code> <code>/*+ no_merge(emp_view) no_push_pred(emp_view) */ emp.empno</code>

現在把測試SQL改一下，把EMP_VIEW用EMP_VIEW_UNION視圖替換，并把連接配接類型改為内連接配接，再次執行

<code>scott@TEST></code><code>select</code> <code>emp.empno</code>

<code> </code><code>2 </code><code>from</code> <code>emp,emp_view_union</code>

<code> </code><code>3 </code><code>where</code> <code>emp.empno=emp_view_union.empno1</code>

視圖EMP_VIEW_UNION的定義SQL語句中包含UNION ALL，它本身就不能做視圖合并，因而具備了做連接配接謂詞推入的基本條件。這裡外部查詢和視圖EMP_VIEW_UNION的連接配接條件為“emp.empno=emp_view_union.empno1”視圖對基表上的EMPNO列都有索引，雖然這裡的連接配接類型是内連接配接，但對于包含UNION ALL的視圖EMP_VIEW_UNION而言，所有能作連接配接謂詞推入的條件都已具備，意味着Oracle地執行上述SQL時做考慮做連接配接謂詞推入。如果做連接配接謂詞推入，那執行計劃就會走嵌套循環連接配接，并且通路視圖的基表會用上列EMPNO上的索引。

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

<code>scott@TEST></code><code>select</code> <code>/*+ no_push_pred(emp_view_union) */emp.empno</code>

之前提到過，Oracle在做連接配接謂詞推入時會考慮成本，隻有經過連接配接謂詞推入後走嵌套循環連接配接的等價改寫SQL的成本值小于原SQL的成本值時，Oracle才會對目标SQL做連接配接謂詞推入。

現在來驗證一下，在上面的SQL中加入cardinality hint，讓CBO認為外圍查詢的結果集的Cardinality是1萬，這樣就會急劇增加做連接配接謂詞推入後的嵌套循環連接配接的成本，如果Oracle在做連接配接謂詞推入是确實會考慮成本，那麼此時Oracle就一定不會再選擇做連接配接謂詞推入。

<code>scott@TEST></code><code>select</code> <code>/*+ cardinality(emp 10000) */emp.empno</code>

<code>scott@TEST></code><code>select</code> <code>/*+ cardinality(emp 10000) push_pred(emp_view_union) */emp.empno</code>

下面再看使用了内嵌視圖且連接配接類型為外連接配接的示例：

<code>scott@TEST></code><code>select</code> <code>/*+ no_merge(emp_view_inline) */ emp.empno</code>

<code> </code><code>2 </code><code>from</code> <code>emp,(</code><code>select</code> <code>emp1.empno </code><code>as</code> <code>empno1 </code><code>from</code> <code>emp1) emp_view_inline</code>

<code> </code><code>3 </code><code>where</code> <code>emp.empno=emp_view_inline.empno1(+)</code>

再回到一開始執行的SQL，把外連接配接改為内連接配接，并在其中加入push_pred hint(讓優化器對視圖EMP_VIEW做連接配接謂詞推入)和USE_NL hint

<code>scott@TEST></code><code>select</code> <code>/*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */ emp.empno</code>

<code> </code><code>3 </code><code>where</code> <code>emp.empno=emp_view.empno1</code>

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

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

參考《基于Oracle的SQL優化》

本文轉自hbxztc 51CTO部落格，原文連結http://blog.51cto.com/hbxztc/1905643：，如需轉載請自行聯系原作者

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

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List