oracle表连接------>排序合并连接(Merge Sort Join)_oracle 强制sort merge join-程序员宅基地

技术标签: Oracle10g 数据库SQL优化  oracle Merge Sort Jo  use_merge  

排序合并连接 (Sort Merge Join)是一种两个表在做连接时用排序操作(Sort)和合并操作(Merge)来得到连接结果集的连接方法。

对于排序合并连接的优缺点及适用场景如下:

a,通常情况下,排序合并连接的执行效率远不如哈希连接,但前者的使用范围更广,因为哈希连接只能用于等值连接条件,而排序合并连接还能用于其他连接条件(如<,<=,>.>=)

b,通常情况下,排序合并连接并不适合OLTP类型的系统,其本质原因是对于因为OLTP类型系统而言,排序是非常昂贵的操作,当然,如果能避免排序操作就例外了。

 

oracle表之间的连接之排序合并连接(Merge Sort Join),其特点如下:

1,驱动表和被驱动表都是最多只被访问一次。

2,排序合并连接的表无驱动顺序。

3,排序合并连接的表需要排序,用到SORT_AREA_SIZE。

4,排序合并连接不适用于的连接条件是:不等于<>,like,其中大于>,小于<,大于等于>=,小于等于<=,是可以适用于排序合并连接

5,排序合并连接,如果有索引就可以排除排序。

 

下面我来做个实验来证实如上的结论:

具体的测试基础表请查看本人Blog 如下链接:

oracle表连接之----〉嵌套循环(Nested Loops Join)

 

1,驱动表和被驱动表的访问次数:

SQL> select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id;

SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%use_merge%';
 
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
85u4h9hfqa5ar            0  select sql_id, child_number, sql_text from v$sql where sql_text like '%use_merg
6xph9fhapys39            0  select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id
 
SQL> select * from table(dbms_xplan.display_cursor('6xph9fhapys39',0,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6xph9fhapys39, child number 0
-------------------------------------
 select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id
Plan hash value: 412793182
--------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buf
--------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.07 |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.07 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."ID"="T2"."T1_ID")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       filter("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement
 
26 rows selected

从上面的实验可以看出排序合并连接和HASH连接时一样的,T1和T2 表都只会被访问0次或者1次。

select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2;此语句T1和T2表就会是被访问0次。自己可以做试验测试下。

总结:排序合并连接根本就没有驱动和被驱动表的概念,而嵌套循环连接和哈希连接就要考虑驱动和被驱动表的情况!!

 

2,排序合并的表的驱动顺序

下面是T1为驱动表的执行计划

select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;
select sql_id,child_number,sql_text from v$sql where sql_text like '%from t1,t2 where t1.id=t2.t1_id and t1.num=20%';
SQL> select * from table(dbms_xplan.display_cursor('8z4jvhnnfhxyf',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8z4jvhnnfhxyf, child number 0
-------------------------------------
select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 412793182

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.58 |    3462 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 |2048  (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |    100K|      1 |00:00:00.58 |    3456 |    14M|  1490K|  12M (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    3456 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


23 rows selected.

Elapsed: 00:00:00.01

下面是T2为驱动表的执行计划:

SQL> select * from table(dbms_xplan.display_cursor('bxydvw58bhczf',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bxydvw58bhczf, child number 0
-------------------------------------
select /*+ leading(t2) use_merge(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 1792967693

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:02.20 |    3462 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100K|     21 |00:00:02.20 |    3456 |    14M|  1490K|  12M (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.10 |    3456 |       |       |          |
|*  4 |   SORT JOIN         |      |     21 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 |2048  (0)|
|*  5 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
   5 - filter("T1"."NUM"=20)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


23 rows selected.

Elapsed: 00:00:00.85

从上面的两个执行计划可以看出,无论T1表示驱动表还是被驱动表,效果都是一样的,排序的尺寸一个是2048+12M,一个是12M+2048。

结论:排序合并连接没有驱动的概念,无论哪个表再前面都无所谓。

 

3,排序合并连接的限制

SQL〉explain plan for select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id<>t2.t1_id and t1.num=20;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4016936828

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5000 |  1083K| 82709   (1)| 00:15:10 |
|   1 |  NESTED LOOPS      |      |  5000 |  1083K| 82709   (1)| 00:15:10 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|    10M|   710   (1)| 00:00:08 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |   107 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20 AND TO_CHAR("T1"."ID") LIKE
              TO_CHAR("T2"."T1_ID"))

16 rows selected.

 从上面的执行计划可以看出,优化器走的是NESTED LOOPS JOIN。

SQL> explain plan for  select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id>t2.t1_id and t1.num=20;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 412793182

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  5000 |  1083K|       |  5080   (1)| 00:00:56 |
|   1 |  MERGE JOIN         |      |  5000 |  1083K|       |  5080   (1)| 00:00:56 |
|   2 |   SORT JOIN         |      |     1 |   107 |       |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |   107 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100K|    10M|    25M|  5076   (1)| 00:00:56 |
|   5 |    TABLE ACCESS FULL| T2   |   100K|    10M|       |   710   (1)| 00:00:08 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20)
   4 - access(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
       filter(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))

19 rows selected.

同理可以实验得出:排序合并连接不适用于的连接条件是:不等于<>,like,其中大于>,小于<,大于等于>=,小于等于<=,是可以适用于排序合并连接

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/waterxcfg304/article/details/25873265

智能推荐

Jtest首次使用中遇到的问题-程序员宅基地

文章浏览阅读669次。这两天下载了个parasoft jtest 7.5.72自动化测试工具,此版本for eclipse 3.1+jdk1.5,由于jtest是基于eclipse的一个插件,所以用起来比较方便,不过第一次用,在测试时还是遇到一些问题; 1、首先我机子上只有jdk1.6和eclipse3.3,好在安装jtest后自带jdk1.5,eclipse3.1和jtest是集成的,直接可以用了。 2..._jtest无法生成单元测试报告

springboot事务EnableTransactionManagement_spring boot enabletransactionmanagement-程序员宅基地

文章浏览阅读1.3k次。_spring boot enabletransactionmanagement

Linux 安装swool_linux 安装easyswool-程序员宅基地

文章浏览阅读505次。一、说明 操作系统:CentOS php安装目录:/usr/local/php php.ini配置文件路径:/usr/local/php/etc/php.ini二、安装 1、安装swoolecd /usr/local/srcwget https://github.com/swoole/swoole-src/archive/..._linux 安装easyswool

Linux内核设计与实现——第3章:进程管理_#ifdef config_smp struct llist_node wake_entry; in-程序员宅基地

文章浏览阅读128次。进程概念 程序/进程/线程 fork()+exec()+exit() 进程的数据结构 进程描述符task_struct thread_info,tast_struct,stack三者关系 pid和tgid 进程状态:五种互斥状态 表示进程亲属关系的成员 进程创建3.1 进程(1)概念程序:代码段 进程:不仅限于执行期的程序,还包括其他资源,如打开的文件,挂起的信号,内核内部数据,处理器状态,一个或多个具有内存映射的内存地址空间,一个或多个执行线程 线程._#ifdef config_smp struct llist_node wake_entry; int on_cpu; struct task_s

通用搜索系统整体架构_业界通用搜索架构-程序员宅基地

文章浏览阅读1.1k次,点赞2次,收藏4次。上周梳理了下搜索流程,抽取了通用的流程,先有全貌,然后再对每个环节细化学习。_业界通用搜索架构

还在手动写Bean?赶紧来用GsonFormat吧_android gson bean是手打的吗-程序员宅基地

文章浏览阅读3.2k次。在Android开发中避免不了和Server端的童鞋打交道,请求接口以后Server端一般返回的都是一个Json串信息,我们拿到Json串然后通过JsonObject或者JsonArray转换成我们需要的Bean,项目中我们一般使用Google的Gson或者阿里的FastJson去处理Json串,他们都是通过反射将Json串转换成Bean,那么我们需要做的就是根据返回的Json串去写对应的Bean类_android gson bean是手打的吗

随便推点

Windows窗体应用点击按钮显示一个新的窗体_vs点击按钮打开另一个窗体-程序员宅基地

文章浏览阅读2.5k次。新建Windows窗体应用在VS中新建一个窗体应用,如Form1选中行项目——新增一个Windows窗体应用,如Form2在Form1的设计器中添加一个button控件设置该button控件的单击事件单击事件代码 Form2 fm2 = new Form2(); fm2.ShowDialog();运行..._vs点击按钮打开另一个窗体

Python标准库之正则表达式(re库)_正则表达式标准库re-程序员宅基地

文章浏览阅读757次。正则表达式从入门到精通_正则表达式标准库re

Ubuntu在使用apt-get update时出现Failed to fetch_sudo apt upgrade显示failed to fetch-程序员宅基地

文章浏览阅读2.2k次。在使用Ubuntu安装工具时,sudo apt-get install xinetd 结果出现了404 not found,而后上网查询问题,建议更新软件列表,使用sudo apt-get update,但此时就出现了一堆错误,Failed to fetch在此之前,使用更新软件列表命令是没有出现过问题的。。。根据网友提供的方法:1、更换软件源,原先使用的是163的软件源,在software & updates里更换,让其自动原则slelect best server后再次尝试更新软件列_sudo apt upgrade显示failed to fetch

第十五章-ARM体系结构基础(1)_arm的index如何判断-程序员宅基地

文章浏览阅读564次。作者:罗宇哲,中国科学院软件研究所智能软件研究中心鲲鹏处理器采用ARM架构,欧拉系统可以运行在鲲鹏架构的服务器上,想要了解欧拉操作系统对硬件的支持,首先需要了解一些ARM架构的基础知识。操作系统中硬件相关的部分集中体现在汇编指令和对寄存器的操作中,因此我们对ARM体系结构的介绍也围绕ARMv8-A的汇编指令和寄存器来展开。处理器架构是处理器厂商为同一个系列的处理器规定的一个规范。ARM架构是一..._arm的index如何判断

【USB接口】USB-Type-A B C 、Micro-USB、Mini-USB接口描述_usb a to usb b 使用描述-程序员宅基地

文章浏览阅读9.8k次,点赞12次,收藏40次。一、USB-Type-A_usb a to usb b 使用描述

互联网晚报 | 07月02日 星期六 | ​​​北京健康宝核酸检测天数计算规则调整;​上海鼓励用人单位吸纳失业3个月及以上人员...-程序员宅基地

文章浏览阅读1.3k次。北京健康宝核酸检测天数计算规则调整近日,北京健康宝发布新版,结合北京市最新防疫政策及用户反馈,对扫码结果展示、到访人登记簿、疫苗接种状态等功能进行优化升级。调整后,健康宝核酸检测天数严格按照日历日计算,并以采样日作为计算起点。每日凌晨0-6时出具检测结果的,通常是在前一日完成采样,当日24时前,健康宝核酸检测日期均显示为1天;24时后查询,则显示2天。以此类推。(北京日报..._a电子商务企业成立于2011年12月,是一家致力于传统企业转型电商的产品销售型、完善