关于表联结方法_sort-merge join-白红宇

在比较经典的表联结方法中，nested loop join和hash join是比较常用的，对于sort-merge join来说，可能略微有些陌生。

在数据库中有一个隐含参数，默认是开启的。

NAME VALUE ISDEFAULT ISMOD ISADJ

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

_optimizer_sortmerge_join_enabled TRUE TRUE FALSE FALSE

因为这种联结方式如果使用不当回消耗大量的系统资源，在一些生产系统中都选择手动禁用这种联结。

这种联结的运行原理想比nested loop join,hash join而言没有驱动表的说法，所以sort-merge join会可能产生大量的随机读。

比如我们有表emp,dept,

查询语句为

select empno,ename,dname,loc from emp,dept where emp.deptno =dept.deptno

如果采用sort-merge join的时候，就会对emp,dept表进行order by 的操作。

类似下面两个操作

select empno,ename ,deptno from emp order by deptno;

select deptno,dname,loc from dept order by deptno;

因为排序后的数据都是有序的，然后对两个子结果集根据deptno进行匹配。

然后选择两端的数据列，根据列的要求筛选数据。

我们先来看一个使用sort-merge join的执行计划，实际中可能需要用到sort-merge join的场景就是类似

tab1.column1 between tab2.column2 and tab2.column3 这种形式的查询中。

我们可以使用hint ordered或者 use_merge来引导查询走sort-merge join ,简单模拟一下。

使用hint ordered

SQL> select /*+ordered*/ empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and dept.deptno+10

2 /

39 rows selected.

Execution Plan

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

Plan hash value: 667632632

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

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

| 0 | SELECT STATEMENT | | 39 | 1287 | 6 (34)| 00:00:01 |

| 1 | MERGE JOIN | | 39 | 1287 | 6 (34)| 00:00:01 |

| 2 | SORT JOIN | | 14 | 182 | 3 (34)| 00:00:01 |

| 3 | TABLE ACCESS FULL | EMP | 14 | 182 | 2 (0)| 00:00:01 |

|* 4 | FILTER | | | | | |

|* 5 | SORT JOIN | | 4 | 80 | 3 (34)| 00:00:01 |

| 6 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("EMP"."DEPTNO")

5 - access(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)

filter(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)

Statistics

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

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

2210 bytes sent via SQL*Net to client

541 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

39 rows processed

可以看到对emp,dept都做了全表扫描，对数据进行了排序，然后对根据deptno对结果集进行了匹配和关联，最后把结果集输出。

也可以使用hint use_merge来实现一样的效果。

SQL> select /*+use_merge(dept,emp)*/ empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and dept.deptno+10

2 /

39 rows selected.

Execution Plan

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

Plan hash value: 1726864587

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

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

| 0 | SELECT STATEMENT | | 39 | 1287 | 6 (34)| 00:00:01 |

| 1 | MERGE JOIN | | 39 | 1287 | 6 (34)| 00:00:01 |

| 2 | SORT JOIN | | 4 | 80 | 3 (34)| 00:00:01 |

| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 2 (0)| 00:00:01 |

|* 4 | FILTER | | | | | |

|* 5 | SORT JOIN | | 14 | 182 | 3 (34)| 00:00:01 |

| 6 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("EMP"."DEPTNO" 5 - access("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)

filter("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)

Statistics

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

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

1796 bytes sent via SQL*Net to client

541 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

39 rows processed

合并排序的思路和数据结构中的合并排序算法，在数据筛选条件有限或者返回有限数据行的查询比较合适。如果本身表中的数据量很大，做sort-merge join就会耗费大量的cpu资源，临时表空间，相比来说不是很划算，完全可以通过其他的联结方式来实现。