排序合并连接(sort merge join)的原理

这三类表连接方式是oracle最基本的连接方式：
嵌套循环连接(nested loops join)原理  
哈希连接(hash join) 原理   

排序合并连接(sort merge join)

    访问次数：两张表都只会访问0次或1次。
    驱动表是否有顺序：无。
    是否要排序：是。
    应用场景：当结果集已经排过序。

排序合并连接原理：如果A表的数据为（2,1,4,5,2）,B表的数据为(2,2,1,3,1) ,首先将A表和B表全扫描后排序，如下：

              A    B

              1     1

              2     1

              2     2

              4     2

              5     3

        因为没有驱动表，所以oracle会随机选择一张表驱动，如果选择了A扫描到1，然后扫描B，当扫描=1的时候则管理，当扫描到B=2时，再以B=2为驱动扫描A表，不是从1开始扫，而是从2开始扫描，交替的进行扫描、关联。

       下面我们来做个试验：

SQL> set linesize 1000
SQL> drop table test1 purge;
SQL> drop table test2 purge;
SQL> create table test1 as select * from dba_objects where rownum <=100;
SQL> create table test2 as select * from dba_objects where rownum <=1000;
SQL> exec dbms_stats.gather_table_stats(user,'test1');
SQL> exec dbms_stats.gather_table_stats(user,'test2');
SQL> alter session set statistics_level=all;
SQL> select /*+ ordered use_merge(t2)*/count(*)
      from test1 t1, test2 t2
     where t1.object_id = t2.object_id;
  COUNT(*)
----------
       100

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f8ffscp1kugv4, child number 0
-------------------------------------
select /*+ ordered use_merge(t2)*/count(*)   from test1 t1, test2 t2  where t1.object_id = t2.object_id

Plan hash value: 737852259

----解释一下：

Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。
Used-Mem耗的内存
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |       |      1 |      1 |      1 |00:00:00.01 |      19 |       | &n