Oracle-多表連接的三種方式解讀

概述

在多表聯合查詢的時候，如果我們查看它的執行計劃，就會發現里面有多表之間的連接方式。

下面是從10053事件中截取的部分trace原文件。

Join order[2]: T[T]#1 T1[T1]#0 ........省略 NL Join ........ Best NL cost: 574.45 resc: 574.45 resc_io: 567.00 resc_cpu: 213015937 resp: 574.45 resp_io: 567.00 resc_cpu: 213015937 ..........省略 SM Join SM cost: 10.08 resc: 10.08 resc_io: 9.00 resc_cpu: 30818220 resp: 10.08 resp_io: 9.00 resp_cpu: 30818220 ..........省略 HA Join HA cost: 9.10 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323 resp: 9.10 resp_io: 9.00 resp_cpu: 2810323 Join order aborted: cost > best plan cost ***********************

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

可以發現多表之間的連接方式有三種連接方式NL Join 、 SM Join、 HA Join。

對應

Nested Loops，Hash Join 和 Sort Merge Join.

三種連接方式

NESTED LOOP

對于被連接的數據子集較小的情況，嵌套循環連接是個較好的選擇。

在嵌套循環中，內表被外表驅動，外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大（大于1 萬不適合），要把返回子集較小表的作為外表（CBO 默認外表是驅動表），而且在內表的連接字段上一定要有索引。當然也可以用ORDERED 提示來改變CBO默認的驅動表，

使用USE_NL(table_name1 table_name2)可是強制CBO 執行嵌套循環連接。

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

步驟：確定一個驅動表(outer table)，另一個表為inner table，驅動表中的每一行與inner表中的相應記錄JOIN。類似一個嵌套的循環。

適用于驅動表的記錄集比較小（<10000）而且inner表需要有有效的訪問方法（Index）。

需要注意的是：JOIN的順序很重要，驅動表的記錄集一定要小，返回結果集的響應時間是最快的。

Sort Merge Join

通常情況下散列連接的效果都比排序合并連接要好，然而如果行源已經被排過序，在執行排序合并連接時不需要再排序了，這時排序合并連接的性能會優于散列連接。

可以使用USE_MERGE(table_name1 table_name2)來強制使用排序合并連接.

Sort Merge join 用在沒有索引，并且數據已經排序的情況.

步驟：將兩個表排序，然后將兩個表合并。

通常情況下，只有在以下情況發生時，才會使用此種JOIN方式：

1.RBO模式

2.不等價關聯(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4.數據源已排序

Hash Join

散列連接（Hash Join ）是CBO 做大數據集連接時的常用方式，

優化器使用兩個表中較小的表（或數據源）利用連接鍵在內存中建立散列表，然后掃描較大的表并探測散列表，找出與散列表匹配的行。

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

也可以用USE_HASH(table_name1 table_name2)提示來強制使用散列連接.

Hash join用在兩個表的數據量差別很大的時候.

步驟：將兩個表中較小的一個在內存中構造一個HASH表（對JOIN KEY），掃描另一個表，同樣對JOIN KEY進行HASH后探測是否可以JOIN。適用于記錄集比較大的情況。

需要注意的是：如果HASH表太大，無法一次構造在內存中，則分成若干個partition，寫入磁盤的temporary segment，則會多一個寫的代價，會降低效率。

三種連接工作方式比較

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

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

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

Oracle

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。