?

三種連接工作方式比較：

Nested loops?工作方式是從一張表中讀取數據，訪問另一張表（通常是索引）來做匹配，nested loops適用的場合是當一個關聯表比較小的時候，效率會更高。

Merge Join?是先將關聯表的關聯列各自做排序，然后從各自的排序表中抽取數據，到另一個排序表中做匹配，因為merge join需要做更多的排序，所以消耗的資源更多。?通常來講，能夠使用merge join的地方，hash join都可以發揮更好的性能。

Hash join的工作方式是將一個表（通常是小一點的那個表）做hash運算，將列數據存儲到hash列表中，從另一個表中抽取記錄，做hash運算，到hash列表中找到相應的值，做匹配。

?

NESTED LOOP?嵌套循環連接（一般用在連接的表中有索引，并且索引選擇性較好的時候.）
1、塊嵌套循環連接，就是把已經放在內存的兩個關系塊全部連接完成后再進行下一數據庫塊的比較，減少內存中數據塊的IO；

2、索引嵌套循環連接，如果內層關系有索引，使用索引代替文件掃描，如果兩個關系均有索引，一般把元組較少的關系作為外層關系時效果好。

嵌套循環連接的工作方式：

在嵌套循環連接中,Oracle從第一個行源中讀取第一行,然后和第二個行源中的數據進行對比。所有匹配的記錄放在結果集中,然后Oracle將讀取第一個行源中的下一行。按這種方式直至第一個數據源中的所在行都經過處理。

??? ??? 在驅動行源表(就是您正在查找的記錄)較小、或者內部行源表已連接的列有惟一的索引或高度可選的非惟一索引時, 嵌套循環連接效果是比較理想的。嵌套循環連接比其他連接方法有優勢,它可以快速地從結果集中提取第一批記錄,而不用等待整個結果集完全確定下來。

nested loop就是掃描一個表，每讀到一條記錄，就根據索引去另一個表里面查找，沒有索引一般就不會是 nested loops。?一般在nested loop中，?驅動表滿足條件結果集不大，被驅動表的連接字段要有索引，這樣就走nstedloop。如果驅動表返回記錄太多，就不適合nested loops了。如果連接字段沒有索引，則適合走hash join，因為不需要索引。?可用ordered提示來改變CBO默認的驅動表，可用USE_NL(table1, table2)提示來強制使用nested loop。

select e.empno, e.ename, e.job, d.dname

from emp e, dept d

where e.deptno = d.deptno and e.empno = 7900;

解析：在這個查詢中，優化器選擇emp作為驅動表，根據唯一性索引PK_EMP快速返回符合條件empno為7900的記錄，然后再與被驅動表dept的deptno關聯查詢相應的dname并最終返回結果集。由于dept表上面的deptno有唯一索引PK_DEPT，故查詢能夠快速地定位deptno對應dname為SALES的記錄并返回。

嵌套循環連接驅動表的選擇也是連接中需要著重注意的一點，有一個常見的誤區是驅動表要選擇小表，其實這是不對的。假如有兩張表A、B關聯查詢，A表有1000000條記錄，B表有10000條記錄，但是A表過濾出來的記錄只有10條，這時候顯然用A表當做驅動表是比較合適的。因此驅動表是由過濾條件限制返回記錄最少的那張表，而不是根據表的大小來選擇的。

?

SORT MERGE JOIN排列合并連接（Sort Merge join?用在沒有索引，并且數據已經排序的情況.）

???????? 在排列合并連接中,Oracle分別將第一個源表、第二個源表按它們各自要連接的列排序,然后將兩個已經排序的源表合并。如果找到匹配的數據,就放到結果集中。

??? ????在缺乏數據的選擇性或者可用的索引時,或者兩個源表都過于龐大(超過記錄數的5%)時,排序合并連接將比嵌套循環連更加高效。但是,排列合并連接只能用于等價連接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno>=E.deptno)。排列合并連接需要臨時的內存塊,以用于排序(如果SORT_AREA_SIZE設置得太小的話)。這將導致在臨時表空間占用更多的內存和磁盤I/O。

sort merge join的操作通常分三步：

1、對連接的每個表做table access full;

2、對table access full的結果進行排序。

3、進行merge join對排序結果進行合并。

在全表掃描比索引范圍掃描再通過rowid進行表訪問更可取的情況下，merge join會比nested loops性能更佳。當表特別小或特別巨大的時候，實行全表訪問可能會比索引范圍掃描更有效。mrege join的性能開銷幾乎都在前兩步。

?

HASH JOIN哈希連接?（Hash join在兩個表的數據量差別很大的時候.）

???????? 當內存能夠提供足夠的空間時,哈希(HASH)連接是Oracle優化器通常的選擇。散列連接是CBO做大數據集連接時的常用方式，優化器使用兩個表中較小的表（或數據源）利用連接鍵在內存中建立散列表，然后掃描較大的表并探測散列表，找出與散列表匹配的行。

只有在數據庫初始化參數HASH_JOIN_ENABLED設為True,并且為參數PGA_AGGREGATE_TARGET設置了一個足夠大的值的時候,Oracle才會使用哈希邊連接。

這種方式適用于較小的表完全可以放于內存中的情況，這樣總成本就是訪問兩個表的成本之和。但是在表很大的情況下并不能完全放入內存，這時優化器會將它分割成若干不同的分區，不能放入內存的部分就把該分區寫入磁盤的臨時段，此時要有較大的臨時段從而盡量提高I/O 的性能。也可以用USE_HASH(table_name1 table_name2)提示來強制使用散列連接。

?

?

?

轉載于:https://www.cnblogs.com/toughhou/p/3778741.html

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的Nested Loop,Sort Merge Join,Hash Join的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。