- 浏览: 65620 次
- 性别:
- 来自: 广州
-
文章分类
最新评论
-
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 836从纵横五个方面精细 ... -
使用SQLPLUS分析SQL语句（查询执行计划跟踪）
2010-11-10 19:16 1088方法一：autotrace1， c ... -
SQL 语句调优_SQL传递参数的数据类型与表索引字段类型保持一致
2010-10-27 20:12 1171SQL 语句调优_SQL传递参数的数据类型与表索引字段类型保持 ... -
oracle正则表达式
2010-10-27 19:30 892在网上找到的一份关于oracle正则表达式,感觉不错，跟大家分 ... -
一个oracle根据特定字符截取数据
2010-10-26 16:33 2130sql split 创建用于存储分开的值容器：create o ... -
操作数据常用用方法
2010-05-26 19:30 812序列 SQL> create sequence seq ... -
隐式游标的参数
2010-05-26 19:20 866当执行一条DML语句后，DML语句的结果保存在四个游标属性中， ...
相关推荐
解释计划(Explain Plan)是一种查看SQL语句执行计划的方法，它提供了关于查询如何执行的详细信息，包括访问方法、估计行数、置信度级别等。通过分析解释计划，我们可以发现潜在的问题点，比如不合理的索引使用或者不...
- 性能调优：当自动优化器选择的执行计划不理想时，可以通过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章 动手，把握需求改写让...
总结起来，“合并记录”和“Merge Join”组件在Kettle中扮演着关键角色，它们能够高效地执行数据增量迁移，显著提高数据同步的速度和准确性。通过充分利用这些组件，IT专业人员可以更有效地管理和维护大规模数据系统...
### 解剖SQL Server执行计划 #### 一、执行计划概览 在深入探讨SQL Server执行计划之前，我们首先需要理解什么是执行计划以及它为何如此重要。执行计划是SQL Server优化器为查询选择的最佳运行策略。当数据库...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
"Merge Join" Step就是其中之一，它允许用户实现类似SQL中的各种类型关联查询，包括INNER JOIN、LEFT JOIN、RIGHT JOIN以及FULL JOIN。 INNER JOIN是SQL中的一种基本联接类型，它返回两个表中存在匹配的记录。在...
本文将详细介绍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操作的步骤是串行的。尽管可以...
另外，可以通过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]; ...