SWAP_JOIN_INPUTS Oracle Hint(處理hash join強制大表（segment_size大）作為被驅動表)

swap_join_inputs是針對哈希連接配接的hint，它的含義是讓優化器交換原哈希連接配接的驅動表和被驅動表的順序，即在依然走哈希連接配接的情況下讓原哈希連接配接的驅動表變被驅動表，讓原哈希連接配接的被驅動表變為驅動表。

注意，在swap_join_inputs hint中指定的目标表應該是原哈希連接配接中的被驅動表，否則oracle會忽略該hint。

/*+ swap_join_inputs(原哈希連接配接的被驅動表) */

其使用範例如下：

<code>select</code> <code>/*+ leading(dept) use_hash(emp) swap_join_intputs(emp) */ * </code><code>from</code> <code>emp,dept </code><code>where</code>

<code>emp.deptno=dept.deptno</code>

測試案例：

<code>SCOTT@ORA12C> </code><code>create</code> <code>table</code> <code>t1 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects </code><code>where</code> <code>rownum<2;</code>

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

<code>SCOTT@ORA12C> </code><code>create</code> <code>table</code> <code>t2 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects </code><code>where</code> <code>rownum<12;</code>

<code>SCOTT@ORA12C> </code><code>create</code> <code>table</code> <code>t3 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects </code><code>where</code> <code>rownum<22;</code>

收集統計資訊：

<code>SCOTT@ORA12C> </code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname => </code><code>'SCOTT'</code><code>,tabname => </code><code>'T1'</code><code>,estimate_percent => 100,</code><code>cascade</code> <code>=> </code><code>true</code><code>,method_opt => </code><code>'for all columns size 1'</code><code>,no_invalidate => </code><code>false</code><code>);</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>SCOTT@ORA12C> </code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname => </code><code>'SCOTT'</code><code>,tabname => </code><code>'T2'</code><code>,estimate_percent => 100,</code><code>cascade</code> <code>=> </code><code>true</code><code>,method_opt => </code><code>'for all columns size 1'</code><code>,no_invalidate => </code><code>false</code><code>);</code>

<code>SCOTT@ORA12C> </code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname => </code><code>'SCOTT'</code><code>,tabname => </code><code>'T3'</code><code>,estimate_percent => 100,</code><code>cascade</code> <code>=> </code><code>true</code><code>,method_opt => </code><code>'for all columns size 1'</code><code>,no_invalidate => </code><code>false</code><code>);</code>

3個表的記錄如下：

<code>SCOTT@ORA12C> </code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t1;</code>

<code> </code><code>COUNT</code><code>(*)</code>

<code>1 row selected.</code>

<code>SCOTT@ORA12C> </code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t2;</code>

<code>SCOTT@ORA12C> </code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t3;</code>

現在我們來讓表T2和T3做哈希連接配接，由于T3表的記錄數比T2表的記錄數多，是以這裡指定T3為哈希連接配接的被驅動表：

<code>select</code> <code>/*+ ordered use_hash(t3) */ t2.object_name,t3.object_type</code>

<code> </code><code>2 </code><code>from</code> <code>t2,t3 </code><code>where</code> <code>t2.object_id=t3.object_id;</code>

<code>Execution Plan</code>

<code>Plan hash value: 1730954469</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 11 | 220 |6 (0)| 00:00:01 |</code>

<code>| 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T2 | 11 | 110 |3 (0)| 00:00:01 |</code>

<code>| 3 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T3 | 21 | 210 |3 (0)| 00:00:01 |</code>

<code>Predicate Information (identified </code><code>by</code> <code>operation id):</code>

<code> </code><code>1 - access(</code><code>"T2"</code><code>.</code><code>"OBJECT_ID"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_ID"</code><code>)</code>

可以看到，上述SQL的執行計劃現在走的是哈希連接配接，并且被驅動表示表T3.

如果我們想讓哈希連接配接的被驅動表由T3變成T2，可以在上述sql加入swap_join_inputs hint：

<code>select</code> <code>/*+ ordered use_hash(t3) swap_join_inputs(t3) */ t2.object_name,t3.object_type</code>

<code>Plan hash value: 1723280936</code>

<code>| 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T3 | 21 | 210 |3 (0)| 00:00:01 |</code>

<code>| 3 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T2 | 11 | 110 |3 (0)| 00:00:01 |</code>

用leading(t3) use_hash(t2)也可以同樣達到目的：

<code>select</code> <code>/*+ leading(t3) use_hash(t2) */ t2.object_name,t3.object_type</code>

由此可見在兩個表關聯的時候，可以用其他hint代替swap_join_inputs來達到相同的目的：

那麼多表關聯呢：

<code>select</code> <code>/*+ ordered use_hash(t3) */ t1.owner,t2.object_name,t3.object_type</code>

<code> </code><code>2 </code><code>from</code> <code>t2,t3,t1 </code><code>where</code> <code>t2.object_id=t3.object_id </code><code>and</code> <code>t1.object_type=t3.object_type;</code>

<code>Plan hash value: 98820498</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 4 | 120 | 9 (0)| 00:00:01 |</code>

<code>| 3 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T2 |11 | 110 | 3 (0)| 00:00:01 |</code>

<code>| 4 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T3 |21 | 210 | 3 (0)| 00:00:01 |</code>

<code>| 5 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| T1 | 1 |10 | 3 (0)| 00:00:01 |</code>

<code> </code><code>1 - access(</code><code>"T1"</code><code>.</code><code>"OBJECT_TYPE"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_TYPE"</code><code>)</code>

<code> </code><code>2 - access(</code><code>"T2"</code><code>.</code><code>"OBJECT_ID"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_ID"</code>

可以看到，現在上述sql的執行計劃是先由表T2和表T3做哈希連接配接，然後将他們做哈希連接配接的連接配接結果集再和表T1做一次哈希連接配接。

表T1的記錄數為1，表T2的記錄數為11，表T3的記錄數為21，是以當表的T2和T3做哈希連接配接時，記錄數多的表T3應該是被驅動表，這是因為我們在上述sql中使用了ordered hint和use_hash HINT指定表T3作為表T2和T3連接配接的時的被驅動表，是以oracle這裡選擇了表T2和T3做哈希連接配接，并且選擇了表T3作為該哈希連接配接的被驅動表，這是沒有問題的，現在問題在于表T1的記錄數僅為1，是以當表T2和T3做哈希連接配接的結果再和表T1做哈希連接配接時，表T1應該是驅動表，而不是在上述執行計劃裡顯示的那樣作為第二個哈希連接配接的被驅動表。

使用下面HINT:

<code> </code><code>2 </code><code>from</code> <code>t1,t2,t3 </code><code>where</code> <code>t2.object_id=t3.object_id </code><code>and</code> <code>t1.object_type=t3.object_type;</code>

<code>Plan hash value: 38266800</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 4 | 120 | 9 (0)| 00:00:01 |</code>

<code>| 2 | MERGE </code><code>JOIN</code> <code>CARTESIAN| | 11 | 220 | 6 (0)| 00:00:01 |</code>

<code>| 3 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| T1 | 1 | 10 | 3 (0)| 00:00:01 |</code>

<code>| 4 | BUFFER SORT | | 11 | 110 | 3 (0)| 00:00:01 |</code>

<code>| 5 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| T2 | 11 | 110 | 3 (0)| 00:00:01 |</code>

<code>| 6 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| T3 | 21 | 210 | 3 (0)| 00:00:01 |</code>

<code> </code><code>1 - access(</code><code>"T2"</code><code>.</code><code>"OBJECT_ID"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_ID"</code> <code>AND</code>

<code> </code><code>"T1"</code><code>.</code><code>"OBJECT_TYPE"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_TYPE"</code><code>)</code>

<code>select</code> <code>/*+ leading(t1) use_hash(t3) */ t1.owner,t2.object_name,t3.object_type</code>

<code>Plan hash value: 2308542799</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 7 | 210 | 9 (0)| 00:00:01 |</code>

<code>| 3 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T1 | 1 |10 | 3 (0)| 00:00:01 |</code>

<code>| 5 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| T2 |11 | 110 | 3 (0)| 00:00:01 |</code>

<code> </code><code>2 - access(</code><code>"T1"</code><code>.</code><code>"OBJECT_TYPE"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_TYPE"</code><code>)</code>

加入以下hint，就解決:

<code>SELECT</code> <code>/*+ ordered use_hash(t3) swap_join_inputs(t1) */</code>

<code> </code><code>t1.owner, t2.object_name, t3.object_type</code>

<code> </code><code>WHERE</code> <code>t2.object_id = t3.object_id</code>

<code> </code><code>5 </code><code>AND</code> <code>t1.object_type = t3.object_type;</code>

<code>Plan hash value: 3071514789</code>

<code>| 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| T1 | 1 |10 | 3 (0)| 00:00:01 |</code>

<code>| 4 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T2 |11 | 110 | 3 (0)| 00:00:01 |</code>

<code>| 5 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T3 |21 | 210 | 3 (0)| 00:00:01 |</code>

<code> </code><code>3 - access(</code><code>"T2"</code><code>.</code><code>"OBJECT_ID"</code><code>=</code><code>"T3"</code><code>.</code><code>"OBJECT_ID"</code><code>)</code>

轉：http://7642644.blog.51cto.com/7632644/1699902

文章可以轉載，必須以連結形式标明出處。

本文轉自張沖andy 部落格園部落格，原文連結： http://www.cnblogs.com/andy6/p/6790184.html ，如需轉載請自行聯系原作者

SWAP_JOIN_INPUTS Oracle Hint(處理hash join強制大表（segment_size大）作為被驅動表)

繼續閱讀

龍珠訓練營task04

Perl與網絡監控

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

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

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

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

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

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

關于SQL語言

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

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List