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

張衝andy發表於2017-04-28
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 資訊>

### Hash table overall statistics ###
Total buckets: 16384 Empty buckets: 9306 Non-empty buckets: 7078
Total number of rows: 9232
Maximum number of rows in a bucket: 5
Average number of rows in non-empty buckets: 1.304323

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


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31383567/viewspace-2138159/,如需轉載,請註明出處,否則將追究法律責任。

相關文章