- 浏览: 65952 次
- 性别:
- 来自: 广州
-
文章分类
最新评论
-
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 840从纵横五个方面精细 ... -
使用SQLPLUS分析SQL语句（查询执行计划跟踪）
2010-11-10 19:16 1096方法一：autotrace1， c ... -
SQL 语句调优_SQL传递参数的数据类型与表索引字段类型保持一致
2010-10-27 20:12 1178SQL 语句调优_SQL传递参数的数据类型与表索引字段类型保持 ... -
oracle正则表达式
2010-10-27 19:30 900在网上找到的一份关于oracle正则表达式,感觉不错，跟大家分 ... -
一个oracle根据特定字符截取数据
2010-10-26 16:33 2133sql split 创建用于存储分开的值容器：create o ... -
操作数据常用用方法
2010-05-26 19:30 818序列 SQL> create sequence seq ... -
隐式游标的参数
2010-05-26 19:20 874当执行一条DML语句后，DML语句的结果保存在四个游标属性中， ...
相关推荐
解释计划(Explain Plan)是一种查看SQL语句执行计划的方法，它提供了关于查询如何执行的详细信息，包括访问方法、估计行数、置信度级别等。通过分析解释计划，我们可以发现潜在的问题点，比如不合理的索引使用或者不...
总结起来，“合并记录”和“Merge Join”组件在Kettle中扮演着关键角色，它们能够高效地执行数据增量迁移，显著提高数据同步的速度和准确性。通过充分利用这些组件，IT专业人员可以更有效地管理和维护大规模数据系统...
- 性能调优：当自动优化器选择的执行计划不理想时，可以通过Hint强制采用特定的执行计划。 - 问题诊断：DBA可以通过Hint了解不同执行计划对查询性能的影响。 - 特殊需求：某些情况下，业务逻辑需要特定的执行路径...
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章 动手，把握需求改写让...
### 解剖SQL Server执行计划 #### 一、执行计划概览 在深入探讨SQL Server执行计划之前，我们首先需要理解什么是执行计划以及它为何如此重要。执行计划是SQL Server优化器为查询选择的最佳运行策略。当数据库...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
本文将详细介绍Oracle SQL中的“提示”（HINT）技术，这是Oracle SQL优化中使用的一项辅助手段，通过为查询提供额外的优化指导，帮助优化器生成更好的执行计划。 在开始讨论具体的SQL提示之前，我们先来理解什么是...
sql学习 Merge Sort Join优化第4式(保证PGA尺寸).sql
执行计划是数据库解析SQL语句后生成的一种详细步骤，它展示了数据是如何被检索、排序、连接等操作的。通过对执行计划的深入理解，我们可以识别出SQL语句的性能瓶颈，并采取相应的优化策略。下面将详细介绍如何通过...
传统的Join操作算法有NestLoopJoin、MergeJoin和HashJoin等，这些算法在集中式环境下表现良好，但当被应用于分布式环境下时，由于网络延迟和数据传输的开销，它们的性能往往不如预期。 Semi-Join算法是为了解决...
sql学习 Merge Sort Join优化第2式(连接条件索引消除排序).sql
sql学习 Merge Sort Join优化第1式(两表限制条件有索引).sql
分析 SQL 语句的执行计划是调优的关键一步骤。可以使用 EXPLAIN PLAN 语句来查看 SQL 语句的执行计划，了解 SQL 语句的执行路径、索引的使用情况、表的连接方式等。 三、选择合适的连接方式 在 Oracle 中，存在...
sql学习 Merge Sort Join优化第3式(避免取多余列致排序尺寸过大).sql
1. 执行计划：每条SQL语句都有一个对应的执行计划，描述了数据如何被获取和处理。Oracle使用基于成本的优化器（CBO）来生成执行计划。 2. 统计信息：CBO依赖于准确的表、索引和其他对象的统计信息来估算执行计划的...
在本文中，我们将深入探讨通过分析SQL语句的执行计划来优化JOIN的方法，特别是关注JOIN过程中的顺序和连接类型。 JOIN过程通常涉及两个row source（表），并且在大多数情况下，JOIN操作的步骤是串行的。尽管可以...
"Merge Join" Step就是其中之一，它允许用户实现类似SQL中的各种类型关联查询，包括INNER JOIN、LEFT JOIN、RIGHT JOIN以及FULL JOIN。 INNER JOIN是SQL中的一种基本联接类型，它返回两个表中存在匹配的记录。在...
另外，可以通过EXPLAIN PLAN或SQL Profiler分析查询执行计划，找出低效的操作。 四、子查询与连接优化 子查询和连接操作是SQL中的常见操作，但处理不当会导致性能下降。考虑使用JOIN操作代替子查询，或者使用连接...
在执行计划中，MERGE INNER JOIN3操作符将被展示为： ```sql #NSET2: [29, 100000, 0] #PRJT2: [29, 100000, 0]; exp_num(2), is_atom(FALSE) #MERGE INNER JOIN3: [29, 100000, 0]; #CSCN2: [23, 100000, 0]; ...