关于表联结方法(二) (r4笔记第23天)

在比较经典的表联结方法中,nested loop join和hash join是比较常用的,对于sort-merge join来说,可能略微有些陌生。 在数据库中有一个隐含参数,默认是开启的。

NAME                                    VALUE                   ISDEFAULT ISMOD       ISADJ
-------------------------------------  ------------------------ --------- ----------  -----
_optimizer_sortmerge_join_enabled       TRUE                    TRUE      FALSE       FALSE

因为这种联结方式如果使用不当回消耗大量的系统资源,在一些生产系统中都选择手动禁用这种联结。 这种联结的运行原理想比nested loop join,hash join而言没有驱动表的说法,所以sort-merge join会可能产生大量的随机读。 比如我们有表emp,dept, 查询语句为 select empno,ename,dname,loc from emp,dept where emp.deptno =dept.deptno 如果采用sort-merge join的时候,就会对emp,dept表进行order by 的操作。 类似下面两个操作 select empno,ename ,deptno from emp order by deptno; select deptno,dname,loc from dept order by deptno; 因为排序后的数据都是有序的,然后对两个子结果集根据deptno进行匹配。 然后选择两端的数据列,根据列的要求筛选数据。 我们先来看一个使用sort-merge join的执行计划,实际中可能需要用到sort-merge join的场景就是 类似 tab1.column1 between tab2.column2 and tab2.column3 这种形式的查询中。 我们可以使用hint ordered或者 use_merge来引导查询走sort-merge join ,简单模拟一下。 使用hint ordered

SQL> select /*+ordered*/   empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and  dept.deptno+10
  2  /
39 rows selected.
Execution  Plan
----------------------------------------------------------
Plan hash  value: 667632632
-----------------------------------------------------------------------------
|  Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------
|    0 | SELECT STATEMENT     |      |    39 |  1287 |     6  (34)| 00:00:01 |
|    1 |  MERGE JOIN          |       |    39 |  1287 |     6  (34)| 00:00:01 |
|   2 |   SORT JOIN          |      |    14 |    182 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | EMP  |    14 |    182 |     2   (0)| 00:00:01 |
|*  4 |   FILTER             |      |        |       |            |          |
|*  5 |    SORT JOIN         |      |     4 |     80 |     3  (34)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| DEPT |     4 |     80 |     2   (0)| 00:00:01  |
-----------------------------------------------------------------------------
Predicate Information (identified by operation  id):
---------------------------------------------------
   4 - filter("EMP"."DEPTNO"<="DEPT"."DEPTNO"+10)
    5 - access(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)
        filter(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)
Statistics
----------------------------------------------------------
           0  recursive calls
          0  db block gets
          4  consistent  gets
          0  physical reads
          0  redo size
       2210   bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from  client
          4  SQL*Net roundtrips to/from client
          2  sorts  (memory)
          0  sorts (disk)
         39  rows  processed

可以看到对emp,dept都做了全表扫描,对数据进行了排序,然后对根据deptno对结果集进行了匹配和关联,最后把结果集输出。 也可以使用hint use_merge来实现一样的效果。

SQL> select /*+use_merge(dept,emp)*/ empno,ename,dname,loc from emp,dept  where emp.deptno between dept.deptno-10  and dept.deptno+10
  2  /
39 rows selected.
Execution  Plan
----------------------------------------------------------
Plan hash  value: 1726864587
-----------------------------------------------------------------------------
|  Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------
|    0 | SELECT STATEMENT     |      |    39 |  1287 |     6  (34)| 00:00:01 |
|    1 |  MERGE JOIN          |      |    39 |  1287 |     6  (34)| 00:00:01 |
|    2 |   SORT JOIN          |      |     4 |    80 |     3  (34)| 00:00:01 |
|    3 |    TABLE ACCESS FULL | DEPT |     4 |    80 |     2   (0)| 00:00:01 |
|*   4 |   FILTER             |      |       |       |            |          |
|*   5 |    SORT JOIN         |      |    14 |   182 |     3  (34)| 00:00:01 |
|    6 |     TABLE ACCESS FULL| EMP  |    14 |   182 |     2   (0)| 00:00:01  |
-----------------------------------------------------------------------------
Predicate Information (identified by operation  id):
---------------------------------------------------
   4 - filter("EMP"."DEPTNO"<="DEPT"."DEPTNO"+10)
   5 -  access("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)
        filter("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)
Statistics
----------------------------------------------------------
           0  recursive calls
          0  db block gets
          4  consistent  gets
          0  physical reads
          0  redo size
       1796   bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from  client
          4  SQL*Net roundtrips to/from client
          2  sorts  (memory)
          0  sorts (disk)
         39  rows processed

合并排序的思路和数据结构中的合并排序算法,在数据筛选条件有限或者返回有限数据行的查询比较合适。如果本身表中的数据量很大,做sort-merge join就会耗费大量的cpu资源,临时表空间,相比来说不是很划算,完全可以通过其他的联结方式来实现。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-01-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jed的技术阶梯

Hive窗口函数05-GROUPING SETS、GROUPING__ID、CUBE、ROLLUP

Hive窗口函数GROUPING SETS、GROUPING__ID、CUBE、ROLLUP入门

22320
来自专栏算法channel

SQL|语句执行逻辑

01 SQL SQL,脚本查询语言,处理代码的顺序不是按照脚本语言的顺序,这点是不同于其他编程语言的最明显特征。 SQL语言常见的比如,Mysql,HiveQ...

38070
来自专栏从ORACLE起航,领略精彩的IT技术。

Oracle之SQL优化专题01-查看SQL执行计划的方法3.1 dbms_xplan.display_cursor(null,null,'allstats last')3.2 dbms_xplan.

51450
来自专栏杨建荣的学习笔记

生产系统调优之_敢于质疑(90天)

接着昨天的那个问题来说。有个sql语句在做了统计信息收集之后,速度有了一定的提升,从5秒的响应降低到了2秒。但是和预期还是有一定 的差距,按照80条查询请求在短...

26570
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(三) (r3笔记第69天)

关于查询转换,已经讨论了视图合并和子查询解嵌套,还有谓词推进和物化视图查询重写也是查询转换中不可或缺的部分。 -->谓词推进 这个术语听起来高大上,有点故弄玄虚...

287110
来自专栏数据和云

又见程序媛 | 从索引的创建角度分析热门“面试题”

关于周一 Eygle 在文章《千头万绪:从一道面试题看数据库性能和安全的方方面面》讲到的 SELECT* FROM girls WHERE age BETWEE...

16840
来自专栏杨建荣的学习笔记

使用Oracle中的emp,dept来学习Django ORM

学习Django的时候,总是觉得这部分内容和实际的应用有一定的差别或者距离。一方面Django自带的ORM对于底层数据库来说是一种适配性很强的组件,可以...

33060
来自专栏lgp20151222

索引之单列索引和组合索引

若有组合索引(a,b,c),那么根据最左前缀,数据库成立了三个索引(a)(a,b)(a,b,c),

14730
来自专栏乐沙弥的世界

SQL 基础-->创建和管理表

(列名 数据类型 [ default 默认值] [ 约束条件] [ , ......] )

5610
来自专栏黑泽君的专栏

day43_Oracle学习笔记_02

7520

扫码关注云+社区

领取腾讯云代金券