关于表联结方法(二) (r4笔记第23天)
关于表联结方法(二) (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资源，临时表空间，相比来说不是很划算，完全可以通过其他的联结方式来实现。