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

標籤：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的視圖

[email protected]>create table emp1 as select * from emp;Table created.[email protected]>create table emp2 as select * from emp;Table created.[email protected]>create index idx_emp1 on emp1(empno);Index created.[email protected]>create index idx_emp2 on emp2(empno);Index created.[email protected]>create or replace view emp_view as   2  select emp1.empno as empno1 from emp1;View created.[email protected]>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

[email protected]>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。

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M01/8E/8D/wKiom1jFCEDhvanbAABNzKuYWHU391.png" title="1.png" alt="wKiom1jFCEDhvanbAABNzKuYWHU391.png" />

從執行計畫上可以看出，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做串連謂詞推入)再次執行

[email protected]>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

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/8E/8C/wKioL1jFCy2ioDq5AABDWHmpFFg163.png" title="1.png" alt="wKioL1jFCy2ioDq5AABDWHmpFFg163.png" />執行計畫已經變為了HASH JOIN OUTER，而且對EMP_VIEW的基表EMP1確實用的是全表掃描。

現在把測試SQL改一下，把EMP_VIEW用EMP_VIEW_UNION視圖替換，並把連線類型改為內串連，再次執行

[email protected]>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上的索引。

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M01/8E/8D/wKiom1jFDRnwBei4AABPpd24qCs026.png" title="1.png" alt="wKiom1jFDRnwBei4AABPpd24qCs026.png" />從執行計畫中可以看出，Oracle走的執行計畫與預想的一樣。

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

[email protected]>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

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/8E/8C/wKioL1jFDhqwjiz0AABQe02iOwQ174.png" title="1.png" alt="wKioL1jFDhqwjiz0AABQe02iOwQ174.png" />從執行計畫可以看出，不使用串連謂詞推入，則對視圖的基表做的是全表掃描。

之前提到過，Oracle在做串連謂詞推入時會考慮成本，只有經過串連謂詞推入後走嵌套迴圈串連的等價改寫SQL的成本值小於原SQL的成本值時，Oracle才會對目標SQL做串連謂詞推入。

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

[email protected]>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

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M00/8E/8C/wKioL1jFECnwwmWMAABQOeTi_Sg264.png" title="1.png" alt="wKioL1jFECnwwmWMAABQOeTi_Sg264.png" />

[email protected]>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

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/8E/8E/wKiom1jFEDqz5GsmAABYll0j4ac217.png" title="2.png" alt="wKiom1jFEDqz5GsmAABYll0j4ac217.png" />從上面的測試可以看出使用cardinality hint後Oracle沒有選擇做串連謂詞推入，此時的成本為10，使用push_pred強製做串連謂詞推入，看到成本為20008。這也驗證了之前說的Oracle在做串連謂詞推入會考慮成本。

下面再看使用了內嵌視圖且連線類型為外串連的樣本：

[email protected]>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

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M01/8E/8E/wKiom1jFEdeQOQt1AABJAsoD2BM714.png" title="1.png" alt="wKiom1jFEdeQOQt1AABJAsoD2BM714.png" />對於上面的SQL，所有能做串連謂詞推入的條件都已具備，從執行計畫中也可以看出Oracle確實也做了串連謂詞推入。

再回到一開始執行的SQL，把外串連改為內串連，並在其中加入push_pred hint(讓最佳化器對視圖EMP_VIEW做串連謂詞推入)和USE_NL hint

[email protected]>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

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M02/8E/8C/wKioL1jFEkSiCKs0AABB9k1Ta0g175.png" title="2.png" alt="wKioL1jFEkSiCKs0AABB9k1Ta0g175.png" />

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

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

參考《基於Oracle的SQL最佳化》

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

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1905643

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