Oracle表连接操作——Merge Sort Join（合并排序连接）

关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。

1、Merge Sort Join原理机制

Nest Loop Join嵌套循环是一种比较古老的连接匹配方式，特点是通过两层的循环结构，将符合条件的数据行整理出来。嵌套循环的最大缺陷之一，就是伴随着驱动表被驱动表之间的选择，以及大量随机读现象。

Merge Sort Join连接的优势就是可以一定程度上减少随机读的情况。合并排序连接的最大特征是在一次扫描的同时，就判断连接。不会像Nest Loop Join那样频繁的进行数据读取。使用这种方式的前提，就是连接的两个数据集合必须按照连接列的顺序进行排序。具体操作流程如下：



ü       对Merge Sort Join连接而言，不存在驱动表和被驱动表的问题。两边的数据集合没有顺序区别，都要进行排序操作；

ü       根据Oracle排序规则和方法，按照连接列的顺序对两个数据集合进行排序；

ü       依次对两边的数据集合进行扫描，由于已经是排序过得结果，可以直接确定连接条件是否匹配；

ü       确定进行连接的两端数据行，再依据筛选列的要求获取数据；



下面是一个进行Merge Sort Join的执行计划：



//使用Merge Sort Join方法

SQL>
select /*+use_merge(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;

已选择865行。


执行计划
----------------------------------------------------------
Plan hash value: 3475644097

------------------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |  990 |  354K|      |  144  (2)| 00:00:02 |
|  1 | MERGE JOIN        |     |  990 |  354K|      |  144  (2)| 00:00:02 |
|  2 |  SORT JOIN        |     |  968 |  229K|  712K|   65  (2)| 00:00:01 |
|  3 |   TABLE ACCESS FULL| TABS |  968 |  229K|      |   11  (0)| 00:00:01 |
|* 4 |  SORT JOIN        |     | 2267 |  274K|  824K|   79  (2)| 00:00:01 |
|  5 |   TABLE ACCESS FULL| SEGS | 2267 |  274K|      |   13  (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
      filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
统计信息
----------------------------------------------------------
      2010 recursive calls
         0 db block gets
       378 consistent gets
         0 physical reads
         0 redo size
     72346 bytes sent via SQL*Net to client
      1003 bytes received via SQL*Net from client
        59 SQL*Net roundtrips to/from client
        10 sorts (memory)
         0 sorts (disk)
       865 rows processed


//使用嵌套循环；

SQL>select /*+use_nl(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;

已选择865行。
执行计划
----------------------------------------------------------
Plan hash value: 840690564

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |  990 |  354K| 11075  (1)| 00:02:13 |
|  1 | NESTED LOOPS     |     |  990 |  354K| 11075  (1)| 00:02:13 |
|  2 |  TABLE ACCESS FULL| TABS |  968 |  229K|   11  (0)| 00:00:01 |
|* 3 |  TABLE ACCESS FULL| SEGS |    1 |  124 |   11  (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  3 - filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
统计信息
----------------------------------------------------------
      1930 recursive calls
         0 db block gets
     43978 consistent gets
         0 physical reads
         0 redo size
     70556 bytes sent via SQL*Net to client
      1003 bytes received via SQL*Net from client
        59 SQL*Net roundtrips to/from client
         6 sorts (memory)
         0 sorts (disk)
       865 rows processed




上面代码示例中给出了两个执行计划，给我们如下的信息。



首先，我们观察使用use_merge提示的SQL，在Hint的作用下，CBO生成的执行计划中使用Merge Sort Join连接方式。在执行计划中Oracle对两个数据表进行Sort操作，之后对排序过的结果进行Merge连接。其中Oracle对两个数据表进行的都是全表扫描操作。





另一个执行计划是使用use_nl控制的Nest Loop Join连接方式。中间同样也是没有使用索引等方式。其中，产生了大量逻辑读。见下表对比：



对比项目
Merge Sort Join
NestLoopJoin

逻辑读consistent gets
378
43978

排序空间sort
10
6




通过数据信息的对比，我们可以明显的看出两个相同结果集合的SQL，由于不同的连接方式而带来的差异。Merge Sort Join可以大大消除由于Nest Loop Join带来的随机读过多的情况。而由于进行的排序操作，Merge Sort Join也要付出相应的排序空间损耗。



2、Merge Sort Join与排序空间



对Oracle熟悉的朋友们通常对Sort和Group操作是比较敏感的。Sort和Group by都是需要单独对数据集进行的操作，要消耗额外的CPU和内存资源。CPU资源主要消耗在算法排序和结果集合整合上。而内存资源的消耗更加需要关注，排序操作要在专门的PGA排序区内完成。如果PGA中特定的排序大小（pga_aggregat_target:sort_area_size）不足以进行排序操作，也就是说需要排序分组的数据集合特别大的时候，Oracle需要调用Temp表空间的容量来进行操作。





这也就是问题的所在。Temp表空间数据存储位于磁盘中，速度与内存相差很多。所以，当进行排序操作的数据集合很大，会出现性能急剧的下降可能。在实际业务场景中，对海量数据集合的处理、Data Warehouse应用的操作，都可能出现这种情况。



回到Merge Sort Join来，就可以理解这种连接方式的缺陷之处了。要进行Merge Sort Join，其中的Sort过程不可避免。而使用Sort操作带来的优势就是不需要进行过多的随机读。在数据集合量很大的时候，Merge Sort Join的效率可能会很差。





3、对索引路径的借用



在Nest Loop Join中，对连接列进行索引处理，可以很大程度上提升执行计划效率，减少随机读的数量。道理就是借用了索引排序这个现实。而Merge Sort Join对索引的应用效果远不如Nest Loop Join。



索引环境构建：



//索引构建

SQL> create index idx_tabs_name on tabs(table_name);
Index created
 
SQL> create index idx_segs_name on segs(segment_name);
Index created
 
SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);
PL/SQL procedure successfully completed
 
SQL> exec dbms_stats.gather_table_stats(user,'TABS',cascade => true);
PL/SQL procedure successfully completed

执行计划如下：

SQL> explain plan for select/*+use_merge(tabs,segs)*/* from segs,tabs where segs.segment_name=tabs.table_name;
Explained
 

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

--------------------------------------------------------------------------------
Plan hash value: 3475644097

--------------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |  990 |  354K|      |  144  (2)| 00:00:
|  1 | MERGE JOIN        |     |  990 |  354K|      |  144  (2)| 00:00:
|  2 |  SORT JOIN        |     |  968 |  229K|  712K|   65  (2)| 00:00:
|  3 |   TABLE ACCESS FULL| TABS |  968 |  229K|      |   11  (0)| 00:00:
|* 4 |  SORT JOIN        |     | 2267 |  274K|  824K|   79  (2)| 00:00:
|  5 |   TABLE ACCESS FULL| SEGS | 2267 |  274K|      |   13  (0)| 00:00:
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
      filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
18 rows selected




由于Merge Sort Join本身就带有排序的特性，而且返回的结果集合中包括所有字段。所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中。除非进行一些特定列处理。

SQL> explain plan for select/*+use_merge(tabs,segs)*/segs.segment_name,tabs.table_namefrom segs,tabs where segs.segment_name=tabs.table_name;
Explained
 

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

--------------------------------------------------------------------------------
Plan hash value: 712326860
--------------------------------------------------------------------------------
| Id | Operation             | Name         | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |              |  990 | 37620 |    9 (23)| 00
|  1 | MERGE JOIN           |              |  990 | 37620 |    9 (23)| 00
|  2 |  SORT JOIN           |              |  968 | 17424 |    4 (25)| 00
|  3 |   INDEX FAST FULL SCAN| IDX_TABS_NAME |  968 | 17424 |    3  (0)| 00
|* 4 |  SORT JOIN           |              | 2267 | 45340 |    5 (20)| 00
|  5 |   INDEX FAST FULL SCAN| IDX_SEGS_NAME | 2267 | 45340 |    4  (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
      filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
18 rows selected




在对返回结果进行处理的情况下，索引路径会出现的。





4、结论



Merge Sort Join是一种古老经典的排序模型，类似于数据结构时代的合并排序算法。Merge Sort Join引入的最大优势是避免同Nest Loop Join类似的大量随机读现象，但是同时也引入了Sort空间变化的问题。





随着海量数据处理场景的增多，Merge Sort Join暴露出缺陷的机会越来越多。而Nest Loop Join的大量随机读问题，也是可以通过索引等常规手段加以优化。