- 浏览: 74792 次
- 性别:
- 来自: 苏州
-
最新评论
关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。
Nest Loop Join嵌套循环是一种比较古老的连接匹配方式，特点是通过两层的循环结构，将符合条件的数据行整理出来。嵌套循环的最大缺陷之一，就是伴随着驱动表被驱动表之间的选择，以及大量随机读现象。
Merge Sort Join连接的优势就是可以一定程度上减少随机读的情况。合并排序连接的最大特征是在一次扫描的同时，就判断连接。不会像Nest Loop Join那样频繁的进行数据读取。使用这种方式的前提，就是连接的两个数据集合必须按照连接列的顺序进行排序。具体操作流程如下：
ü 对Merge Sort Join连接而言，不存在驱动表和被驱动表的问题。两边的数据集合没有顺序区别，都要进行排序操作；
ü 根据Oracle排序规则和方法，按照连接列的顺序对两个数据集合进行排序；
ü 依次对两边的数据集合进行扫描，由于已经是排序过得结果，可以直接确定连接条件是否匹配；
ü 确定进行连接的两端数据行，再依据筛选列的要求获取数据；
下面是一个进行Merge Sort Join的执行计划：
//使用Merge Sort Join方法
SQL>select /*+use_merge(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;
已选择865行。
执行计划
----------------------------------------------------------
Plan hash value: 3475644097
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 354K| | 144 (2)| 00:00:02 |
| 1 | MERGE JOIN | | 990 | 354K| | 144 (2)| 00:00:02 |
| 2 | SORT JOIN | | 968 | 229K| 712K| 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABS | 968 | 229K| | 11 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 2267 | 274K| 824K| 79 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL| SEGS | 2267 | 274K| | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
统计信息
----------------------------------------------------------
2010 recursive calls
0 db block gets
378 consistent gets
0 physical reads
0 redo size
72346 bytes sent via SQL*Net to client
1003 bytes received via SQL*Net from client
59 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
865 rows processed
//使用嵌套循环；
SQL>select /*+use_nl(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;
已选择865行。
执行计划
----------------------------------------------------------
Plan hash value: 840690564
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 354K| 11075 (1)| 00:02:13 |
| 1 | NESTED LOOPS | | 990 | 354K| 11075 (1)| 00:02:13 |
| 2 | TABLE ACCESS FULL| TABS | 968 | 229K| 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SEGS | 1 | 124 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
统计信息
----------------------------------------------------------
1930 recursive calls
0 db block gets
43978 consistent gets
0 physical reads
0 redo size
70556 bytes sent via SQL*Net to client
1003 bytes received via SQL*Net from client
59 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
865 rows processed
上面代码示例中给出了两个执行计划，给我们如下的信息。
首先，我们观察使用use_merge提示的SQL，在Hint的作用下，CBO生成的执行计划中使用Merge Sort Join连接方式。在执行计划中Oracle对两个数据表进行Sort操作，之后对排序过的结果进行Merge连接。其中Oracle对两个数据表进行的都是全表扫描操作。
另一个执行计划是使用use_nl控制的Nest Loop Join连接方式。中间同样也是没有使用索引等方式。其中，产生了大量逻辑读。见下表对比：
|
对比项目 |
Merge Sort Join |
NestLoopJoin |
|
逻辑读consistent gets |
378 |
43978 |
|
排序空间sort |
10 |
6 |
通过数据信息的对比，我们可以明显的看出两个相同结果集合的SQL，由于不同的连接方式而带来的差异。Merge Sort Join可以大大消除由于Nest Loop Join带来的随机读过多的情况。而由于进行的排序操作，Merge Sort Join也要付出相应的排序空间损耗。
2、Merge Sort Join与排序空间
对Oracle熟悉的朋友们通常对Sort和Group操作是比较敏感的。Sort和Group by都是需要单独对数据集进行的操作，要消耗额外的CPU和内存资源。CPU资源主要消耗在算法排序和结果集合整合上。而内存资源的消耗更加需要关注，排序操作要在专门的PGA排序区内完成。如果PGA中特定的排序大小（pga_aggregat_target:sort_area_size）不足以进行排序操作，也就是说需要排序分组的数据集合特别大的时候，Oracle需要调用Temp表空间的容量来进行操作。
这也就是问题的所在。Temp表空间数据存储位于磁盘中，速度与内存相差很多。所以，当进行排序操作的数据集合很大，会出现性能急剧的下降可能。在实际业务场景中，对海量数据集合的处理、Data Warehouse应用的操作，都可能出现这种情况。
回到Merge Sort Join来，就可以理解这种连接方式的缺陷之处了。要进行Merge Sort Join，其中的Sort过程不可避免。而使用Sort操作带来的优势就是不需要进行过多的随机读。在数据集合量很大的时候，Merge Sort Join的效率可能会很差。
3、对索引路径的借用
在Nest Loop Join中，对连接列进行索引处理，可以很大程度上提升执行计划效率，减少随机读的数量。道理就是借用了索引排序这个现实。而Merge Sort Join对索引的应用效果远不如Nest Loop Join。
索引环境构建：
//索引构建
SQL> create index idx_tabs_name on tabs(table_name);
Index created
SQL> create index idx_segs_name on segs(segment_name);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'TABS',cascade => true);
PL/SQL procedure successfully completed
执行计划如下：
SQL> explain plan for select/*+use_merge(tabs,segs)*/* from segs,tabs where segs.segment_name=tabs.table_name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3475644097
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 354K| | 144 (2)| 00:00:
| 1 | MERGE JOIN | | 990 | 354K| | 144 (2)| 00:00:
| 2 | SORT JOIN | | 968 | 229K| 712K| 65 (2)| 00:00:
| 3 | TABLE ACCESS FULL| TABS | 968 | 229K| | 11 (0)| 00:00:
|* 4 | SORT JOIN | | 2267 | 274K| 824K| 79 (2)| 00:00:
| 5 | TABLE ACCESS FULL| SEGS | 2267 | 274K| | 13 (0)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
18 rows selected
由于Merge Sort Join本身就带有排序的特性，而且返回的结果集合中包括所有字段。所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中。除非进行一些特定列处理。
SQL> explain plan for select/*+use_merge(tabs,segs)*/segs.segment_name,tabs.table_namefrom segs,tabs where segs.segment_name=tabs.table_name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 712326860
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 37620 | 9 (23)| 00
| 1 | MERGE JOIN | | 990 | 37620 | 9 (23)| 00
| 2 | SORT JOIN | | 968 | 17424 | 4 (25)| 00
| 3 | INDEX FAST FULL SCAN| IDX_TABS_NAME | 968 | 17424 | 3 (0)| 00
|* 4 | SORT JOIN | | 2267 | 45340 | 5 (20)| 00
| 5 | INDEX FAST FULL SCAN| IDX_SEGS_NAME | 2267 | 45340 | 4 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
18 rows selected
在对返回结果进行处理的情况下，索引路径会出现的。
4、结论
Merge Sort Join是一种古老经典的排序模型，类似于数据结构时代的合并排序算法。Merge Sort Join引入的最大优势是避免同Nest Loop Join类似的大量随机读现象，但是同时也引入了Sort空间变化的问题。
随着海量数据处理场景的增多，Merge Sort Join暴露出缺陷的机会越来越多。而Nest Loop Join的大量随机读问题，也是可以通过索引等常规手段加以优化。
发表评论
-
Oracle的schema、方案、模式、用户
2012-12-07 12:11 1031今天才对这几个概念 ... -
DBMS_STATS.GATHER_TABLE_STATS详解
2012-08-15 15:12 1685作用:DBMS_STATS.GATHER_TABLE_S ... -
Oracle表连接操作——Nest Loop Join（嵌套循环）
2012-08-15 15:12 7851关系型数据库并不是最 ... -
Oracle hash join
2012-08-15 15:13 982hash join是oracle里面一个 ... -
Oracle表连接操作——Hash Join（哈希连接）下
2012-08-15 15:13 1613Hash Join是Oracle CBO时代经常出现的一 ... -
Oracle表连接操作——Hash Join（哈希连接）上
2012-08-14 14:19 13886在Oracle中，确定连接操作类型是执行计划生成的重要方 ... -
oracle 递归查询，向上和向下遍历
2012-08-14 11:10 19235--查询全部资源信息 select * from urm_cl ... -
ORACLE中Like与Instr性能大比拼（转）
2012-08-14 11:09 915http://zhaolinjnu.blog.sohu.com ... -
SQL语句优化技术分析
2012-08-14 11:08 908一、操作符优化1、IN 操 ... -
CBO和RBO介绍
2012-08-14 11:08 852Rule Based Optimizer（RBO） ... -
Install Oracle10gXE on Ubuntu Server using apt
2012-03-19 17:22 795With Oracle Database XE in prod ... -
Merge into 详细介绍
2012-03-15 15:40 889/*Merge into 详细介绍 MERGE ... -
ora-12516 tns listener could not find available handler with matching protocol
2012-03-12 15:51 8658公司数据库安装的是 Oracle10g XE(Oracle D ... -
CUUG《Oracle 监听和TNS配置》- 笔记
2012-02-24 22:07 2160监听负责多个实例 相关文件：Listener.ora和 ... -
批处理备份oracle
2012-02-24 22:07 791@echo off @title Oracle简易数据备份工具 ... -
oracle-ORA的原理
2012-02-07 14:15 10031、在一个应用环境当中，所有的服务器使用和管理同一个数据库，目 ... -
Oracle的卸载过程
2012-02-07 12:15 845修改注册表，在开始运行中执行regedit命令，进入注册表， ... -
创建表空间文件
2012-02-07 12:12 2200--1 创建表空间文件 --1.1 创建ASIT 实体表空间 ... -
SQL触发器实例讲解
2012-02-07 12:10 808SQL触发器实例1 定义： ... -
oracle 10g 默认用户名密码及解锁
2012-02-07 12:04 9511. 安装时选择的自动安装，忘了用户名和密码，导致现在试了几个 ...
相关推荐
/************合并排序算法的实现******************/ int main() { int p,q,r; printf("合并排序算法的实现:\n"); printf("请输入p、q、r的值(输入格式1,12,13):"); scanf("%d,%d,%d",&p,&q,&r); printf("p=%...
void merge_sort(int A[],int p,int r) { int q; if(p) { q=(p+r)/2;//计算q的值，即将问题拆分成两个子问题； merge_sort(A,p,q); //左半边递归调用merge_sort，缩小问题规模 printf("\n"); //print_A(A...
c++ 分治法合并排序 merge sort c语言 分治法合并排序 merge sort（将cout修改printf 加头文件include "stdio.h")
Merge Sorted Array 合并 排序 数组 leetcode
sql学习 Merge Sort Join优化第2式(连接条件索引消除排序).sql
在对传统的Sort-Merge-Join算法进一步研究的基础上，提出了一种改进的闪存数据库Sort-Merge-Join算法。该算法只对小关系进行外排序，避免了大关系的外排序，节省了大量时间，同时最小化了中间临时表，达到了少写闪存...
sql学习 Merge Sort Join优化第3式(避免取多余列致排序尺寸过大).sql
Oracle的三种表连接方式 详细讲述• sort merge join(SMJ) • nest loop(NL) • hash join(HJ)
博客《ListView滑动删除实现之一——merge标签与LayoutInflater.inflate()》对应源码，博客地址：http://blog.csdn.net/harvic880925/article/details/45155965
实用基础SQL语句；oracle sqlldr;SQL基础语句；merge；分组排序；条件赋值；表连接。简单示例，Oracle数据库文档数据导入
sql学习 Merge Sort Join优化第1式(两表限制条件有索引).sql
排序——归并排序（Merge sort）
归并排序（Merge sort)(台灣譯作：合併排序）是建立在归并操作上的一种有效的排序算法。该算法是采用分治法（Divide and Conquer）的一个非常典型的应用。
sql学习 Merge Sort Join优化第4式(保证PGA尺寸).sql
oracle同一个表中使用merge
用merge算法合并两个已排序的表，望对大家有帮助，谢谢！
分治法求解排序问题 合并为其中一种 效率不错 时间复杂度为O nlogn 代码有本人大量解释 便于理解
通过merge-sort算法的实现，掌握外存算法所基于的I/O模型与内存算法基于的RAM模型的区别；理解不同的磁盘访问优化方法是如何提高数据访问性能的。