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

对比项目

Merge Sort Join

Nest Loop Join

逻辑读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);

SQL> exec dbms_stats.gather_table_stats(user,'SEGS',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

--------------------------------------------------------------------------------

| 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:

18 rows selected

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

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

--------------------------------------------------------------------------------

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

4、结论

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!