oracle表串連------>排序合并串連(Merge Sort Join)__oracle

排序合并串連 (Sort Merge Join)是一種兩個表在做串連時用排序操作(Sort)和合併作業(Merge)來得到串連結果集的串連方法。

對於排序合并串連的優缺點及適用情境如下：

a,通常情況下，排序合并串連的執行效率遠不如雜湊串連，但前者的使用範圍更廣，因為雜湊串連只能用於等值串連條件，而排序合并串連還能用於其他串連條件(如<,<=,>.>=)

b,通常情況下，排序合并串連並不適合OLTP類型的系統，其本質原因是對於因為OLTP類型系統而言，排序是非常昂貴的操作，當然，如果能避免排序操作就例外了。

 

oracle表之間的串連之排序合并串連(Merge Sort Join)，其特點如下：

1，驅動表和被驅動表都是最多隻被訪問一次。

2，排序合并串連的表無驅動順序。

3，排序合并串連的表需要排序，用到SORT_AREA_SIZE。

4，排序合并串連不適用於的串連條件是：不等於<>，like,其中大於>，小於<，大於等於>=，小於等於<=，是可以適用於排序合并串連

5，排序合并串連，如果有索引就可以排除排序。

 

下面我來做個實驗來證實如上的結論：

具體的測試基礎資料表請查看本人Blog 如下連結：

oracle表串連之----〉嵌套迴圈(Nested Loops Join)

 

1，驅動表和被驅動表的訪問次數：

SQL> select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id;

SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%use_merge%';
 
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
85u4h9hfqa5ar            0  select sql_id, child_number, sql_text from v$sql where sql_text like '%use_merg
6xph9fhapys39            0  select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id
 
SQL> select * from table(dbms_xplan.display_cursor('6xph9fhapys39',0,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6xph9fhapys39, child number 0
-------------------------------------
 select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id
Plan hash value: 412793182
--------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buf
--------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.07 |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.07 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."ID"="T2"."T1_ID")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       filter("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement
 
26 rows selected

從上面的實驗可以看出排序合并串連和HASH串連時一樣的，T1和T2 表都只會被訪問0次或者1次。

select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2;此語句T1和T2表就會是被訪問0次。自己可以做實驗測試下。

總結：排序合并串連根本就沒有驅動和被驅動表的概念，而嵌套迴圈串連和雜湊串連就要考慮驅動和被驅動表的情況。。

 

2，排序合并的表的驅動順序

下面是T1為驅動表的執行計畫

select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;
select sql_id,child_number,sql_text from v$sql where sql_text like '%from t1,t2 where t1.id=t2.t1_id and t1.num=20%';
SQL> select * from table(dbms_xplan.display_cursor('8z4jvhnnfhxyf',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8z4jvhnnfhxyf, child number 0
-------------------------------------
select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 412793182

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.58 |    3462 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 |2048  (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |    100K|      1 |00:00:00.58 |    3456 |    14M|  1490K|  12M (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    3456 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

23 rows selected.

Elapsed: 00:00:00.01

下面是T2為驅動表的執行計畫：

SQL> select * from table(dbms_xplan.display_cursor('bxydvw58bhczf',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bxydvw58bhczf, child number 0
-------------------------------------
select /*+ leading(t2) use_merge(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 1792967693

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:02.20 |    3462 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100K|     21 |00:00:02.20 |    3456 |    14M|  1490K|  12M (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.10 |    3456 |       |       |          |
|*  4 |   SORT JOIN         |      |     21 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 |2048  (0)|
|*  5 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
   5 - filter("T1"."NUM"=20)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

23 rows selected.

Elapsed: 00:00:00.85

從上面的兩個執行計畫可以看出，無論T1表示驅動表還是被驅動表，效果都是一樣的，排序的尺寸一個是2048+12M，一個是12M+2048。

結論：排序合并串連沒有驅動的概念，無論哪個表再前面都無所謂。

 

3，排序合并串連的限制

SQL〉explain plan for select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id<>t2.t1_id and t1.num=20;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4016936828

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5000 |  1083K| 82709   (1)| 00:15:10 |
|   1 |  NESTED LOOPS      |      |  5000 |  1083K| 82709   (1)| 00:15:10 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|    10M|   710   (1)| 00:00:08 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |   107 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20 AND TO_CHAR("T1"."ID") LIKE
              TO_CHAR("T2"."T1_ID"))

16 rows selected.

 從上面的執行計畫可以看出，最佳化器走的是NESTED LOOPS JOIN。

SQL> explain plan for  select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id>t2.t1_id and t1.num=20;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 412793182

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  5000 |  1083K|       |  5080   (1)| 00:00:56 |
|   1 |  MERGE JOIN         |      |  5000 |  1083K|       |  5080   (1)| 00:00:56 |
|   2 |   SORT JOIN         |      |     1 |   107 |       |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |   107 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100K|    10M|    25M|  5076   (1)| 00:00:56 |
|   5 |    TABLE ACCESS FULL| T2   |   100K|    10M|       |   710   (1)| 00:00:08 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20)
   4 - access(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
       filter(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))

19 rows selected.

同理可以實驗得出：排序合并串連不適用於的串連條件是：不等於<>，like,其中大於>，小於<，大於等於>=，小於等於<=，是可以適用於排序合并串連