排序合并连接(sort merge 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
——————————————————————————————————————-
| 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 |       |       |          |
|   2 |   MERGE JOIN         |       |      1 |    100 |    100 |00:00:00.01 |      19 |       |       |          |
|   3 |    SORT JOIN         |       |      1 |    100 |    100 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL| TEST1 |      1 |    100 |    100 |00:00:00.01 |       4 |       |       |          |
|*  5 |    SORT JOIN         |       |    100 |   1000 |    100 |00:00:00.01 |      15 | 73728 | 73728 |          |
|   6 |     TABLE ACCESS FULL| TEST2 |      1|   1000 |   1000 |00:00:00.01 |      15 |       |       |          |
——————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
5 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
filter(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

SQL> select /*+ ordered use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id = 99999;
COUNT(*)
———-
0

SQL> select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  gub4203v6y49v, child number 0
————————————-
select /*+ ordered use_merge(t2)*/count(*)   from test1 t1, test2 t2  where t1.object_id = t2.object_id
and t1.object_id = 99999
Plan hash value: 1970191094
——————————————————————————————————————-
| 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 |       4 |       |       |          |
|   2 |   MERGE JOIN         |       |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|*  3 |    TABLE ACCESS FULL | TEST1 |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|*  4 |    SORT JOIN         |       |      0 |      1 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|*  5 |     TABLE ACCESS FULL| TEST2 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
——————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
3 – filter(“T1”.”OBJECT_ID”=99999)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
filter(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
5 – filter(“T2”.”OBJECT_ID”=99999)

SQL> select /*+ ordered use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and 1=2;
COUNT(*)
———-
0

SQL> select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID  3duvzmx0wm2hv, child number 0
————————————-
select /*+ ordered use_merge(t2)*/count(*)   from test1 t1, test2 t2  where t1.object_id =
t2.object_id    and 1=2
Plan hash value: 593691543
———————————————————————————————————-
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
———————————————————————————————————-
|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |       |       |       |
|*  2 |   FILTER              |       |      1 |        |      0 |00:00:00.01 |       |       |       |
|   3 |    MERGE JOIN         |       |      0 |    100 |      0 |00:00:00.01 |       |       |       |
|   4 |     SORT JOIN         |       |      0 |    100 |      0 |00:00:00.01 | 73728 | 73728 |       |
|   5 |      TABLE ACCESS FULL| TEST1 |      0 |    100 |      0 |00:00:00.01 |       |       |       |
|*  6 |     SORT JOIN         |       |      0 |   1000 |      0 |00:00:00.01 | 73728 | 73728 |       |
|   7 |      TABLE ACCESS FULL| TEST2 |      0 |   1000 |      0 |00:00:00.01 |       |       |       |
———————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – filter(NULL IS NOT NULL)
6 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
filter(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

标签