排序合并连接（SORT MERGE JOIN）

前文提到 HASH 连接主要用于处理两表等值关联返回大量数据。

排序合并连接主要用于处理两表非等值关联，比如 >，>=，<，<=，<>，但是不能用于 instr、substr、like、regexp_like 关联，instr、substr、like、regexp_like 关联只能走嵌套循环。

现有如下 SQL。

select * from a,b where a.id>=b.id;

A 表有 10 万条数据，B 表有 20 万条数据，A 表与 B 表的 ID 列都是从 1 开始每次加 1。

该 SQL 是非等值连接，因此不能进行 HASH 连接。

假如该 SQL 走的是嵌套循环，A 作为驱动表，B 作为被驱动表，那么 B 表会被扫描 10 万次。前文提到，嵌套循环被驱动表连接列要包含在索引中，那么 B 表的 ID 列需要创建一个索引，嵌套循环会进行传值，当 A 表通过 ID 列传值超过 10 000 的时候，B 表通过 ID 列的索引返回数据每次都会超过 10 000 条，这个时候会造成 B 表大量回表。所以该 SQL 不能走嵌套循环，只能走排序合并连接。

排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle 官方认为排序合并连接没有驱动表，笔者认为是有的），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA 中的 work area）匹配数据。

我们在测试账号 scott 中运行如下 SQL。

select /*+ gather_plan_statistics */  e.ename， e.job，
  d.dname   from emp e， dept d  where e.deptno >= d.deptno；

我们获取执行计划。

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  f673my5x7tkkg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  e.ename, e.job, d.dname   from
emp e, dept d  where e.deptno >= d.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------
| Id |Operation                    |Name   |Starts|E-Rows|A-Rows|   A-Time  |Buffers|
-------------------------------------------------------------------------------------
|  0 |SELECT STATEMENT             |       |     1|      |    31|00:00:00.01|     15|
|  1 | MERGE JOIN                  |       |     1|     3|    31|00:00:00.01|     15|
|  2 |  TABLE ACCESS BY INDEX ROWID|DEPT   |     1|     4|     4|00:00:00.01|      8|
|  3 |   INDEX FULL SCAN           |PK_DEPT|     1|     4|     4|00:00:00.01|      4|
|* 4 |  SORT JOIN                  |       |     4|    14|    31|00:00:00.01|      7|
|  5 |   TABLE ACCESS FULL         |EMP    |     1|    14|    14|00:00:00.01|      7|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO">="D"."DEPTNO")
   filter("E"."DEPTNO">="D"."DEPTNO")

执行计划中离 MERGE JOIN 关键字最近的表就是驱动表。这里 DEPT 就是驱动表，EMP 就是被驱动表。驱动表 DEPT 只扫描了一次（Id=2，Starts=1），被驱动表 EMP 也只扫描了一次（Id=5,Starts=1）。

因为 DEPT 走的是 INDEX FULL SCAN，INDEX FULL SCAN 返回的数据是有序的，所以 DEPT 表就不需要排序了。EMP 走的是全表扫描，返回的数据是无序的，所以 EMP 表在 PGA 中进行了排序。在实际工作中，我们一定要注意 INDEX FULL SCAN 返回了多少行数据，如果 INDEX FULL SCAN 返回的行数太多，应该强制走全表扫描，具体原因请参考《常见访问路径》。

现在我们强制 DEPT 表走全表扫描，查看执行计划。

select /*+ full(d) */
  e.ename, e.job, d.dname
    from emp e, dept d
   where e.deptno >= d.deptno;

31 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1407029907
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    90 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |     3 |    90 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    52 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |    14 |   238 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |   238 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO">="D"."DEPTNO")
       filter("E"."DEPTNO">="D"."DEPTNO")

从执行计划中我们看到，DEPT 走的是全表扫描，因为全表扫描返回的数据是无序的，所以 DEPT 在 PGA 中进行了排序。

如果两表是等值关联，一般不建议走排序合并连接。因为排序合并连接需要将两个表放入 PGA 中，而 HASH 连接只需要将驱动表放入 PGA 中，排序合并连接与 HASH 连接相比，需要耗费更多的 PGA。即使排序合并连接中有一个表走的是 INDEX FULL SCAN，另外一个表也需要放入 PGA 中，而这个表往往是大表，如果走 HASH 连接，大表会作为被驱动表，是不会被放入 PGA 中的。因此，两表等值关联，要么走 NL（返回数据量少），要么走 HASH（返回数据量多），一般情况下不要走 SMJ。

思考：怎么优化排序合并连接？

回答：如果两表关联是等值关联，走的是排序合并连接，我们可以将表连接方式改为 HASH 连接。如果两表关联是非等值关联，比如 >，>=，<，<=，<>，这时我们应该先从业务上入手，尝试将非等值关联改写为等值关联，因为非等值关联返回的结果集「类似」于笛卡儿积，当两个表都比较大的时候，非等值关联返回的数据量相当「恐怖」。如果没有办法将非等值关联改写为等值关联，我们可以考虑增加两表的限制条件，将两个表数据量缩小，最后可以考虑开启并行查询加快 SQL 执行速度。

表 5-1 列举出了 3 种表连接方式的主要区别。

表 5-1　　表连接方式