Nested loops、Hash join、Sort merge join（三種連線型別原理、使用要點）

nested loop 巢狀迴圈（原理）：
oracle從較小結果集（驅動表、也可以被稱為outer）中讀取一行，然後和較大結果集（被偵查表，也可以叫做inner）中的所有資料逐條進行比較（也是等值連線，也可以是非等值連線），如果符合規則，就放在結果集中，然後取驅動表的下一條資料繼續迴圈，直到結束。
簡單的理解就是：從A表抽一條記錄，遍歷B表查詢匹配記錄，然後從a表抽下一條，遍歷B表，就是一個二重迴圈。


使用要點：
1.驅動表的記錄集比較小，一般來說小於驅動表結果集的10% 
2.inner表需要有有效的訪問方法（Index），選擇性要高。


hash join 雜湊連線（原理）： 
表T1和T2在施加了目標SQL中指定的謂詞條件（如果有的話）後得到的結果集中資料量較小的那個結果集（S）會被Oracle選為雜湊連線的驅動結果集，T2所對應的結果集的資料量相對較大，我們記為B；S是驅動結果集，B是被驅動結果集；接著Oracle會遍歷S，讀取S中的每一條記錄，並對S中的每一條記錄按照該記錄在表T1中的連線列做雜湊運算，直到遍歷完S中的所有記錄為止；Oracle會遍歷B，讀取B中的每一條記錄，並對B中的每一條記錄按照該記錄在表T2中的連線列做雜湊運算，最後hash值匹配hash值，返回結果。
簡單的理解：將A表按連線鍵計算出一個hash表，然後從B表一條條抽取記錄，計算hash值，根據hash到A表的hash來匹配符合條件的記錄。


使用要點：
1. hash join在 oltp 環境下一般沒什麼最佳化的地方，在 olap環境中可以並行最佳化 hash join 
2. 返回大量結果集（幾w+）適合走hash join
3. hash join選擇小表作為驅動表，注意這裡的小表不是指表的行數，而是指的 行數*列的寬度，可以簡單的理解為 segment size。
4. hash join會使用到pga中的WORK AREA ， 如果等待事件中有on-disk hash join( direct path read/write temp ),可以加大pga size。

5.hash join不適合驅動表表關聯欄位分佈不均勻的情況。

(可以透過10046跟蹤，然後檢視 Maximum number of rows in a bucket 是否成千上萬< 假象執行計劃沒有問題，但一個超大bucket，將hash join效率極度拉低，消耗大量cpu time   >，一般個數在個位數效能最好，如果一個hash bucket的rows過多，可以改寫sql，改變hash table 的列值  )      10046跟蹤顯示的hash buckets 資訊>

sort merge join 排序合併連線（原理）：
先排序操作(Sort)，再合併操作(Merge)。
簡單的理解：將A，B表都排好序，然後做merge，符合條件的選出。

使用要點：
1. 排序合併連線的表無驅動順序。
2. 排序合併連線不適用於的連線條件是：不等於<>，like,其中大於>，小於<，大於等於>=，小於等於<=，是可以適用於排序合併連線
3. 排序合併連線的表需要排序，用到SORT_AREA_SIZE。
4. 驅動表和被驅動表都是最多隻被訪問一次。

HINT：

操作           Nest Loop       Hash Join             Sort Mereg

Join           USE_NL         USE_HASH            USE_MERGE

Anti Join    NL_AJ            HASH_AJ               MERGE_AJ

Semi          NL_SJ            HASH_SJ              MERGE_SJ