当前位置:Gxlcms > mysql > 排序合并连接(sortmergejoin)的原理

排序合并连接(sortmergejoin)的原理

时间:2021-07-01 10:21:17 帮助过:76人阅读

这三类表连接方式是Oracle最基本的连接方式:嵌套循环连接(nested loops join)原理 哈希连接(hash join) 原理 排序合并连接(sor

这三类表连接方式是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 | | | |
| 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

更多详情见请继续阅读下一页的精彩内容

linux

人气教程排行