排序合并连接（SORTMERGEJOIN）
排序合并连接内部处理的流程：
1）优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则到第2步。
2）第一个源表排序
3）优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则到第4步。
4）第二个源表排序
5）已经排过序的两个源表进行合并操作，并生成最终的结果集。
在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大（所选的数据超过表记录数的5%）时，排序合并连接将比嵌套循环连更加高效。
排列合并连接需要比较大的临时内存块，以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。
可以通过在SQL语句中添加HINTS,强制Oracle优化器产生排序合并连接的执行计划。
select/*+use_merge(ab)*/a.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id>b.user_id;
案例：
11:57:04 SCOTT@ prod> select e.ename,e.sal,d.deptno,d.dname
from dept d,emp e
11:57:13 2 11:57:13 3 where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 294 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 294 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 140 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1042 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
11:57:14 SCOTT@ prod>select /*+ order */ e.ename,e.sal,d.deptno,d.dname
11:58:14 2 from dept d,emp e
11:58:14 3 where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 294 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 294 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 140 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1042 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
11:58:23 SCOTT@ prod>
11:59:48 SCOTT@ prod>