當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP

發(fā)布時(shí)間：2024/4/17 编程问答 34 豆豆

生活随笔收集整理的這篇文章主要介紹了多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

在多表聯(lián)合查詢的時(shí)候，如果我們查看它的執(zhí)行計(jì)劃，就會(huì)發(fā)現(xiàn)里面有多表之間的連接方式。之前打算在sqlplus中用執(zhí)行計(jì)劃的，但是格式看起來(lái)有點(diǎn)亂，就用Toad 做了3個(gè)截圖。

???

從3張圖里我們看到了幾點(diǎn)信息：

1.?????? CBO 使用的ALL_ROWS模式

Oracle Optimizer CBO RBO

http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx

2.?????? 表之間的連接用了hash Join， Nested loops，Sort Merge Join

多表之間的連接有三種方式：Nested Loops，Hash Join 和 Sort Merge Join. 下面來(lái)介紹三種不同連接的不同：

一． NESTED LOOP:

對(duì)于被連接的數(shù)據(jù)子集較小的情況，嵌套循環(huán)連接是個(gè)較好的選擇。在嵌套循環(huán)中，內(nèi)表被外表驅(qū)動(dòng)，外表返回的每一行都要在內(nèi)表中檢索找到與它匹配的行，因此整個(gè)查詢返回的結(jié)果集不能太大（大于1 萬(wàn)不適合），要把返回子集較小表的作為外表（CBO 默認(rèn)外表是驅(qū)動(dòng)表），而且在內(nèi)表的連接字段上一定要有索引。當(dāng)然也可以用ORDERED 提示來(lái)改變CBO默認(rèn)的驅(qū)動(dòng)表，使用USE_NL(table_name1 table_name2)可是強(qiáng)制CBO 執(zhí)行嵌套循環(huán)連接。

????????

Nested loop一般用在連接的表中有索引，并且索引選擇性較好的時(shí)候.

步驟：確定一個(gè)驅(qū)動(dòng)表(outer table)，另一個(gè)表為inner table，驅(qū)動(dòng)表中的每一行與inner表中的相應(yīng)記錄JOIN。類似一個(gè)嵌套的循環(huán)。適用于驅(qū)動(dòng)表的記錄集比較小（<10000）而且inner表需要有有效的訪問(wèn)方法（Index）。需要注意的是：JOIN的順序很重要，驅(qū)動(dòng)表的記錄集一定要小，返回結(jié)果集的響應(yīng)時(shí)間是最快的。

cost = outer access cost + (inner access cost * outer cardinality)

| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
| 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
| 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |

EMPLOYEES為outer table, JOBS為inner table.

二． HASH JOIN :

散列連接是CBO 做大數(shù)據(jù)集連接時(shí)的常用方式，優(yōu)化器使用兩個(gè)表中較小的表（或數(shù)據(jù)源）利用連接鍵在內(nèi)存中建立散列表，然后掃描較大的表并探測(cè)散列表，找出與散列表匹配的行。

這種方式適用于較小的表完全可以放于內(nèi)存中的情況，這樣總成本就是訪問(wèn)兩個(gè)表的成本之和。但是在表很大的情況下并不能完全放入內(nèi)存，這時(shí)優(yōu)化器會(huì)將它分割成若干不同的分區(qū)，不能放入內(nèi)存的部分就把該分區(qū)寫(xiě)入磁盤(pán)的臨時(shí)段，此時(shí)要有較大的臨時(shí)段從而盡量提高I/O 的性能。

也可以用USE_HASH(table_name1 table_name2)提示來(lái)強(qiáng)制使用散列連接。如果使用散列連接HASH_AREA_SIZE 初始化參數(shù)必須足夠的大，如果是9i，Oracle建議使用SQL工作區(qū)自動(dòng)管理，設(shè)置WORKAREA_SIZE_POLICY 為AUTO，然后調(diào)整PGA_AGGREGATE_TARGET 即可。

????????

Hash join在兩個(gè)表的數(shù)據(jù)量差別很大的時(shí)候.

步驟：將兩個(gè)表中較小的一個(gè)在內(nèi)存中構(gòu)造一個(gè)HASH表（對(duì)JOIN KEY），掃描另一個(gè)表，同樣對(duì)JOIN KEY進(jìn)行HASH后探測(cè)是否可以JOIN。適用于記錄集比較大的情況。需要注意的是：如果HASH表太大，無(wú)法一次構(gòu)造在內(nèi)存中，則分成若干個(gè)partition，寫(xiě)入磁盤(pán)的temporary segment，則會(huì)多一個(gè)寫(xiě)的代價(jià)，會(huì)降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
| 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------

ORDERS為HASH TABLE，ORDER_ITEMS掃描

三．SORT MERGE JOIN

通常情況下散列連接的效果都比排序合并連接要好，然而如果行源已經(jīng)被排過(guò)序，在執(zhí)行排序合并連接時(shí)不需要再排序了，這時(shí)排序合并連接的性能會(huì)優(yōu)于散列連接。可以使用USE_MERGE(table_name1 table_name2)來(lái)強(qiáng)制使用排序合并連接.

????????

Sort Merge join 用在沒(méi)有索引，并且數(shù)據(jù)已經(jīng)排序的情況.

cost = (outer access cost * # of hash partitions) + inner access cost

步驟：將兩個(gè)表排序，然后將兩個(gè)表合并。通常情況下，只有在以下情況發(fā)生時(shí)，才會(huì)使用此種JOIN方式：

1.RBO模式

2.不等價(jià)關(guān)聯(lián)(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4.數(shù)據(jù)源已排序

四. ?三種連接工作方式比較：

?????

?????? Hash join的工作方式是將一個(gè)表（通常是小一點(diǎn)的那個(gè)表）做hash運(yùn)算，將列數(shù)據(jù)存儲(chǔ)到hash列表中，從另一個(gè)表中抽取記錄，做hash運(yùn)算，到hash 列表中找到相應(yīng)的值，做匹配。

????????

Nested loops 工作方式是從一張表中讀取數(shù)據(jù)，訪問(wèn)另一張表（通常是索引）來(lái)做匹配，nested loops適用的場(chǎng)合是當(dāng)一個(gè)關(guān)聯(lián)表比較小的時(shí)候，效率會(huì)更高。

???????? Merge Join 是先將關(guān)聯(lián)表的關(guān)聯(lián)列各自做排序，然后從各自的排序表中抽取數(shù)據(jù)，到另一個(gè)排序表中做匹配，因?yàn)?/span>merge join需要做更多的排序，所以消耗的資源更多。通常來(lái)講，能夠使用merge join的地方，hash join都可以發(fā)揮更好的性能。

整理自網(wǎng)絡(luò)

------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

網(wǎng)上資源： http://tianlesoftware.download.csdn.net

相關(guān)視頻：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(滿); DBA2 群：62697977(滿)

DBA3 群：63306533;???? 聊天群：40132017

轉(zhuǎn)載于:https://www.cnblogs.com/spring3mvc/archive/2010/08/20/2414795.html

總結(jié)

以上是生活随笔為你收集整理的多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。