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。
sort merge join 排序合併連線(原理):
先排序操作(Sort),再合併操作(Merge)。
簡單的理解:將A,B表都排好序,然後做merge,符合條件的選出。
使用要點:
1. 排序合併連線的表無驅動順序。
2. 排序合併連線不適用於的連線條件是:不等於<>,like,其中大於>,小於<,大於等於>=,小於等於<=,是可以適用於排序合併連線
3. 排序合併連線的表需要排序,用到SORT_AREA_SIZE。
4. 驅動表和被驅動表都是最多隻被訪問一次。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- nested loop,sort merge join,hash joinOOP
- 表的連線方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN(轉)OOP
- 多表連線的三種方式詳解 hash join、merge join、 nested loopOOP
- HASH JOIN ,MERGE JOIN ,NESTED LOOPOOP
- nested loops 和hash join的一點測試OOP
- HASH JOIN ,MERGE JOIN ,NESTED LOOP(R2)OOP
- HASH JOIN ,MERGE JOIN ,NESTED LOOP的比較OOP
- hash join\nest loop join\sort merge join的實驗OOP
- 排序合併連線(sort merge join)的原理排序
- Mysql join 的本質,Nested Loop Join 和 Hash JoinMySqlOOP
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- Oracle表連線操作——Merge Sort Join(合併排序連線)Oracle排序
- Oracle 的 hash join連線方式Oracle
- Oracle表連線操作——Hash Join(雜湊連線)下Oracle
- Oracle表連線操作——Hash Join(雜湊連線)上Oracle
- Hash join演算法原理演算法
- mysql INNER JOIN、LEFT JOIN、RIGHT JOIN;內連線(等值連線)、左連線、右連線MySql
- LEFT JOIN 和JOIN 多表連線
- 微課sql最佳化(14)、表的連線方法(3)-關於Nested Loops Join(巢狀迴圈)SQLOOP巢狀
- Hash join演算法原理(轉)演算法
- oracle hash join演算法原理Oracle演算法
- Oracle Sort JoinOracle
- 通過swap_join_inputs來控制多表連線hash join中的build tableUI
- MySql的join(連線)查詢 (三表 left join 寫法)MySql
- ORACLE Hash JoinOracle
- 連線查詢簡析 join 、 left join 、 right join
- oracle hash join原理及注意事項Oracle
- 資料庫實現原理#1(Nested Loop Join)資料庫OOP
- SQL | JOIN 型別使用介紹SQL型別
- 【sql調優之執行計劃】merge sort joinSQL
- Oracle(+)連線與Join連線Oracle
- HASH join詳解
- SQL語句中不同的連線JOIN及SQL中join的各種用法SQL
- 資料庫實現原理#4(Hash Join)資料庫
- join、inner join、left join、right join、outer join的區別
- hash join構建點陣圖的理解
- 外連線(outer join)示例
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解