oracle表之间的连接之------>排序合并连接(Merge Sort Join)

排序合并连接 (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)

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次。自己可以做试验测试下。

总结：排序合并连接根本就没有驱动和被驱动表的概念，而嵌套循环连接和哈希连接就要考虑驱动和被驱动表的情况！！