- 浏览: 64182 次
- 性别:
- 来自: 广州
-
文章分类
最新评论
-
a307487540：
<?xml version="1.0&quo ...
spring 3.0MVC 一个简单demo
【sql调优之执行计划】merge semi join and merge anti join
Semi join（也有叫半连接的）多在子查询in或者exists等中使用，对于外部行集，查找内部（即子查询）行集，匹配第一行之后就返回，不再往下查找例如：
SQL> select b.*
2 from scott.dept b
3 where b.deptno in (select deptno from scott.emp a)
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."DEPTNO"="DEPTNO")
filter("B"."DEPTNO"="DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
614 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
或者：
SQL> select b.*
2 from scott.dept b
3 where exists (select 1 from scott.emp a where a.deptno = b.deptno)
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
614 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
可以看到这种情况下，in和exsits的执行计划完全相同，且都使用了merge join semi的oporation
而not in或者not exists则不同，Oracle7.3版本之前not exists和not in还使用的tilter，merge anti join和hash anti join访问路径是后来增加的。
例子：
SQL> select b.*
2 from scott.dept b
3 where not exists (select 1 from scott.emp a where a.deptno = b.deptno)
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
这里是merge join anti（也叫反连接），和semi相反，只有外部行在内部不能匹配的时候才返回。
而，not in则和not exsits不同，执行计划显示的是filter：
SQL> select b.*
2 from scott.dept b
3 where b.deptno not in (select deptno from scott.emp a)
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 7 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 2 | 6 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE
LNNVL("DEPTNO"<>:B1)))
3 - filter(LNNVL("DEPTNO"<>:B1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
5 physical reads
0 redo size
535 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
我们知道not in与not exsits并不能等同，从执行计划上来看，not in的执行计划的operation是filter，而且内表和外表都是全表，没有使用索引，而从谓词信息中来看，operation 1为：
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE
LNNVL("DEPTNO"<>:B1)))
Null值对not in影响较大，如果稍稍修改一下这个查询，则又有不同了：
SQL> select b.*
2 from scott.dept b
3 where b.deptno not in (select nvl(deptno,0) from scott.emp a);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."DEPTNO"=NVL("DEPTNO",0))
filter("B"."DEPTNO"=NVL("DEPTNO",0))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
奇怪的是，这里使用了索引，因为告诉了oracle不会有null值，而且谓词信息也发生了改变：
4 - access("B"."DEPTNO"=NVL("DEPTNO",0))
这也是使用not in需要注意的地方。
发表评论
-
一编个人觉得关于oracle优化方面写得不错的文章
2010-11-10 19:23 802从纵横五个方面精细 ... -
使用SQLPLUS分析SQL语句（查询执行计划跟踪）
2010-11-10 19:16 1046方法一：autotrace1， c ... -
SQL 语句调优_SQL传递参数的数据类型与表索引字段类型保持一致
2010-10-27 20:12 1138SQL 语句调优_SQL传递参数的数据类型与表索引字段类型保持 ... -
oracle正则表达式
2010-10-27 19:30 863在网上找到的一份关于oracle正则表达式,感觉不错，跟大家分 ... -
一个oracle根据特定字符截取数据
2010-10-26 16:33 2089sql split 创建用于存储分开的值容器：create o ... -
操作数据常用用方法
2010-05-26 19:30 783序列 SQL> create sequence seq ... -
隐式游标的参数
2010-05-26 19:20 838当执行一条DML语句后，DML语句的结果保存在四个游标属性中， ...
相关推荐
在对传统的Sort-Merge-Join算法进一步研究的基础上，提出了一种改进的闪存数据库Sort-Merge-Join算法。该算法只对小关系进行外排序，避免了大关系的外排序，节省了大量时间，同时最小化了中间临时表，达到了少写闪存...
15.1 高级SQL之分析函数 407 15.1.1 语法概述 407 15.1.2 特别之处 407 15.2 分析函数详解与案例 409 15.2.1 学习详解 410 15.2.2 案例分享 417 15.3 本章习题、总结与延伸 432 第16章 动手，把握需求改写让...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
sql学习 Merge Sort Join优化第4式(保证PGA尺寸).sql
sql学习 Merge Sort Join优化第2式(连接条件索引消除排序).sql
sql学习 Merge Sort Join优化第1式(两表限制条件有索引).sql
sql学习 Merge Sort Join优化第3式(避免取多余列致排序尺寸过大).sql
ORACLE SQL语句编写与调优 ORACLE交流群48949977 内容简介 第一部分 SQL基础 SQL简介 发展历史 SQL语句分类（DDL、DML、DQL、DCL） 内容简介 第五部分 SQL优化 优化器 SQL语句执行过程 SQL优化术语 第一...
NULL 博文链接：https://forlan.iteye.com/blog/2245814
数据库 我自己在 Java 中实现了 SortMergeJoin 和 HashJoin（来自 SQL 的著名 INNER JOIN）。 在更多信息。
15.1 高级SQL之分析函数 407 15.1.1 语法概述 407 15.1.2 特别之处 407 15.2 分析函数详解与案例 409 15.2.1 学习详解 410 15.2.2 案例分享 417 15.3 本章习题、总结与延伸 432 第16章 动手，把握需求改写让...
MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子...
Araxis Merge是一款比对软件工具，不仅支持单个文件的比对，而且支持对两个文件夹的所有文件进行对比，并实时修改。用了蛮久感觉很好用，所以分享给大家。 里面给大家内嵌了Araxis Merge v6.5和Araxis Merge 2017两...
Free software for agile handling of Trados Studio tasks.
sql学习 merge灵活之处.sql
Pandas合并DataFrame_Merge,_Join,_Concat,_Append【Pandas入门教程6】
sql学习 merge误区探讨.sql
sql学习 merge经典案例.sql
快速浏览一、append与... update方法（a）三个特点（b）例子三、concat方法四、merge与join1.merge函数2. join函数五、问题与练习阶段总结Reference #从清华镜像拉装1.0.3版本的Pandas !pip install -i https://pypi.t
SQL server 触发器，在触发Merge过程中，逐行触发的解决办法 用group by 避免是一次触发中的多行更新或删除。