我们先看合并连接的定义:通常称为排序合并连接(Sort Merge Join),因为这种连接的要求是:两个结果集是排序的,排序字段是两个结果集的连接字段.
排序合并连接(Sort Merge Join)的执行顺序如下:
1.根据sql文本的where条件,对目标表T1进行访问,对过滤出来的符合where条件的记录按照连接字段进行排序,排好序后的结果集我们记为结果集1
2.根据sql文本的where条件,对目标表T2进行访问,对过滤出来的符合where条件的记录按照连接字段进行排序,排好序后的结果集我们记为结果集2
注意,以上写的是1和2,但是我感觉1和2这两步操作是可以同时进行的.
3.对结果集1和结果集2进行合并操作,从中取出来的匹配的记录是排序合并连接(Sort Merge Join)的最终执行结果.
这个"对结果集1和结果集2进行合并操作"的具体过程,参考崔华的<<基于Oracle的SQL优化>> 第48页
先取出结果集1中的第1条记录,到结果集2中按照连接字段进行匹配,
然后再取出结果集1中的第2条记录,到结果集2中按照连接字段进行匹配
如此反复,直到结果集1中的所有记录遍历完成.
对于排序合并连接(Sort Merge Join)的优缺点及适用场景,也借用崔华的<<基于Oracle的SQL优化>> 第48页
1 通常情况下,排序合并连接(Sort Merge Join)执行效率远不如哈希连接,但是排序合并连接(Sort Merge Join)适用范围更广,
因为哈希连接只能用于等值连接的where条件,而排序合并连接(Sort Merge Join)还能用于其他的连接条件(如< ,<=,>,>=>)
2.通常情况下,排序合并连接(Sort Merge Join)不适合OLTP系统,因为OLTP系统,排序操作是昂贵的,当然,如果能避免排序,用排序合并连接(Sort Merge Join)也是可以的,
比如连接字段存在索引的情况下.
3.严格意义上来说,排序合并连接(Sort Merge Join)是不存在驱动表概念的,虽然我个人认为排序合并连接(Sort Merge Join)在执行时,是存在驱动表和被驱动表的.
上面说完了排序合并连接(Sort Merge Join),其实MERGE JOIN CARTESIAN的执行步骤跟上面的步骤差不多:
1.根据sql文本的where条件,对目标表T1进行访问,对过滤出来的符合where条件的记录我们记为结果集1 ---此结果集1可能是排好序的,也可能是不排序的.
2.根据sql文本的where条件,对目标表T2进行访问,对过滤出来的符合where条件的记录我们记为结果集2 ---此结果集2可能是排好序的,也可能是不排序的.
注意,以上写的是1和2,但是我感觉1和2这两步操作是可以同时进行的.
3.对结果集1和结果集2进行合并操作,由于没有关联字段,因此,对于结果集1中的每一条记录,结果集2中的所有记录均满足条件.见下面的例子:
create user usera identified by aaaaaa;
grant dba to usera;
create table usera.t1 (c1 number,c2 date);
create table usera.t2 (c3 number,c4 date);
declare
i int;
begin
for i in 1 .. 1000000 loop
insert /*+ append */
into usera.t1 nologging
values
(i, sysdate);
if mod(i, 10000) = 0 then
commit;
end if;
end loop;
end;
/
declare
i int;
begin
for i in 10000 .. 50000 loop
insert /*+ append */
into usera.t2 nologging
values
(i, sysdate);
if mod(i, 1000) = 0 then
commit;
end if;
end loop;
end;
/
create usera.IDX_T2_C3 on usera.t2(c3);
create usera.IDX_T1_C1_C2 on usera.t1(c1,c2);
t1表收集统计信息
t2表收集统计信息
SQL> select t1.c1,t1.c2 from usera.t1,usera.t2 where t1.c1=t2.c3 and t2.c3=45678;
C1 C2
---------- ---------
45678 11-DEC-17
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------
SQL_ID cy8a7tfrn0s4z, child number 0
-------------------------------------
select t1.c1,t1.c2 from usera.t1,usera.t2 where t1.c1=t2.c3 and
t2.c3=45678
Plan hash value: 175341246
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 6 | 3 | | | |
| 1 | MERGE JOIN CARTESIAN| | 1 | 1 | 1 |00:00:00.02 | 6 | 3 | | | |
|* 2 | INDEX RANGE SCAN | IDX_T2_C3 | 1 | 1 | 1 |00:00:00.02 | 3 | 1 | | | |
| 3 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 3 | 2 | 2048 | 2048 | 1/0/0|
|* 4 | INDEX RANGE SCAN | IDX_T1_C1_C2 | 1 | 1 | 1 |00:00:00.01 | 3 | 2 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T2@SEL$1
4 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."C3"))
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2"))
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_MERGE_CARTESIAN(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."C3"=45678)
4 - access("T1"."C1"=45678)----->>注意此处,在sql文本中只有t2.c3=45678,为啥这里出现了"T1"."C1"=45678 ???
----->>这是CBO传递闭包(Transitive Closure)之后的结果,说传递闭包可能太高大上,我觉得就是CBO对sql文本的Transformation.
47 rows selected.
SQL>