微課sql最佳化(16)、表的連線方法(5)-關於Merge Join(排序合連線)

orastar發表於2020-03-04
SQL排序

1、原理解釋

2、Merge Join特點

1、每個子節點只會執行一次
2、每個輸入資料集都必須按照連線條件的欄位進行排序
3、由於排序操作，在返回第一條結果前，兩個資料集都必須被完全讀出並排序。

3、 適用場景

Merge Join將兩個表排序，然後將兩個表合併。通常情況下，只有在以下情況發生時，才會使用此種JOIN方式：
1.RBO模式
2.不等價關聯(>,<,>=,<=,<>)
3.HASH_JOIN_ENABLED=false
4.資料來源已排序

4、Merge Join示例

SQL> select  /*+ ordered use_merge(a)*/c.cons_no,c.cons_name,a.AMT_YM,a.amt
from ht.c_cons_hash c,ht.a_amt_hash a
where c.cons_no=a.cons_no
and c.cons_name='Hash_Join'
order by 1;  2    3    4    5  
Execution Plan
----------------------------------------------------------
Plan hash value: 1313733800
--------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |      12 |     360 |         |     401   (1)| 00:00:05 |
|   1 |  MERGE JOIN          |           |      12 |     360 |         |     401   (1)| 00:00:05 |
|   2 |   SORT JOIN          |           |       2 |      26 |         |       4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| C_CONS_HASH         |       2 |      26 |         |       3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_C_CONS_HASH_NAME |       2 |         |         |       1   (0)| 00:00:01 |
|*  5 |   SORT JOIN          |           | 59968 |     995K|   3320K|     397   (1)| 00:00:05 |
|   6 |    TABLE ACCESS FULL          | A_AMT_HASH         | 59968 |     995K|         |      65   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C"."CONS_NAME"='Hash_Join')
   5 - access("C"."CONS_NO"="A"."CONS_NO")
       filter("C"."CONS_NO"="A"."CONS_NO")

5、三種連線方式比較

NL:  從一張表中讀取資料，訪問另一張表（通常是索引）來做匹配，nested loops適用的場合是驅動錶行數較小時，效率會更高。
Hash join： 將一個表（通常是小一點的那個表）對連線鍵做hash運算，將Hash結果做為索引值和行資訊儲存到Hash表中，從另一個表中抽取記錄，對連線鍵做hash運算，到Hash表中查詢匹配的行。
Merge Join 是先將關聯表的關聯列各自做排序，然後從各自的排序表中抽取資料，到另一個排序表中做匹配，因為merge join需要做更多的排序，所以消耗的資源更多。 通常情況下，Hash Join會比 Merge Join效能更好。
 

6、引數文件

《Oracle? Database Performance Tuning Guide 11g Release 2 (11.2)》
《Troubleshooting Oracle Performance》

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

相關文章