zgqynx
- 浏览: 1364647 次
- 性别:
- 来自: 北京
-
文章分类
- 全部博客 (551)
- 计划 (4)
- java (115)
- oracle (60)
- ajax (3)
- javascript (64)
- 计算机操作技巧集 (11)
- 近期关注话题 (10)
- 随想 (13)
- html (6)
- struts (15)
- hibernate (16)
- spring (2)
- game (0)
- Eglish (10)
- DisplayTag (6)
- jsp (18)
- css (3)
- eclipse (3)
- 其他知识 (8)
- 备用1 (12)
- 备用2 (1)
- 笑话－放松心情 (9)
- 设计 (1)
- 设计模式 (1)
- 数据结构 (0)
- office办公软件 (5)
- webwork (0)
- tomcat (2)
- MySql (1)
- 我的链接资源 (5)
- xml (2)
- servlet (0)
- PHP (13)
- DOM (0)
- 网页画图vml,canvas (1)
- 协议 (2)
- 健康 (3)
- 书籍下载 (1)
- jbpm (1)
- EXT (1)
- 自考 (2)
- 报表 (4)
- 生活 (64)
- 操作系统基础知识 (2)
- 测试 (2)
- guice (1)
- google学习 (2)
- Erlang (1)
- LOG4J (2)
- wicket (1)
- 考研 (1)
- 法律 (1)
- 地震 (1)
- 易学-等等相关 (1)
- 音乐 (1)
- 建站 (4)
- 分享说 (3)
- 购物省钱 (0)
- linux (1)
最新评论
-
zenmshuo：
如果使用SpreadJS这一类的表格工具，应该能更好的实现这些 ...
js中excel的用法 -
hjhj2991708：
第一个已经使用不了
jar包查询网站 非常好用！ -
jiangmeiwei：
...
中文乱码 我的总结 不断更新 -
gary_bu：
...
response.sendRedirect 中文乱码问题解决 -
hnez：
多谢指点，怎么调试也不通，原来我在<body>&l ...
ExtJs IE ownerDocument.createRange() 错误解决方案
在平时工作中,IN & EXISTS NOT IN & NOT EXISTS是使用频率比较高的SQL语句,
所以对它们的优化工作是很有必要的
测试环境：Oracle 9.2.0.1 for Windows2000
所以对它们的优化工作是很有必要的
测试环境：Oracle 9.2.0.1 for Windows2000
1、IN 和 EXISTS
IN和EXISTS的处理流程是不一样的：
IN的执行流程
select * from T1 where x in ( select y from T2 )
可以理解为:
select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;
EXISTS的执行流程
select * from t1 where exists ( select null from t2 where y = x )
可以理解为:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
创建测试用例表big(4000 row)和small(400 row)
create table big as select * from dba_objects where rownum <= 10000;
insert into big select * from big;
insert into big select * from big;
commit;
create table small as select * from dba_objects where rownum <= 400;
当内层表为small,外层表为big时,两种语法的查询如下：
SQL> select count(1) from big a where a.object_id in
(select b.object_id from sall b);
COUNT(1)
----------
1600
已用时间: 00: 00: 00.56
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'BIG'
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 SORT (UNIQUE)
8 7 TABLE ACCESS (FULL) OF 'SMALL'
Statistics
-----------------------------------------------------
0 recursive calls
0 db block gets
543 consistent gets
0 physical reads
SQL> select count(1) from big a where exists
(select 1 from small b where a.object_id=b.object_id);
COUNT(1)
----------
1600
已用时间: 00: 00: 03.10
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'BIG'
4 2 TABLE ACCESS (FULL) OF 'SMALL'
Statistics
-----------------------------------------------------
0 recursive calls
0 db block gets
312157 consistent gets
0 physical reads
当内层表为big,外层表为small时,两种语法的查询如下：
SQL> select count(1) from small a where a.object_id in
(select b.object_id from big b);
COUNT(1)
----------
400
已用时间: 00: 00: 00.56
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'SMALL'
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 SORT (UNIQUE)
8 7 TABLE ACCESS (FULL) OF 'BIG'
Statistics
-----------------------------------------------------
0 recursive calls
0 db block gets
543 consistent gets
0 physical reads
SQL> select count(1) from small a where exists
(select null from big b where a.bject_id=b.object_id);
COUNT(1)
----------
400
已用时间: 00: 00: 00.25
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SMALL'
4 2 TABLE ACCESS (FULL) OF 'BIG'
Statistics
-----------------------------------------------------
0 recursive calls
0 db block gets
2562 consistent gets
0 physical reads
在对表big、small进行分析后,发现CBO下两种语法的执行计划是一样的,都使用hash连接或者hash半连接
SQL> analyze table big compute statistics;
SQL> analyze table small compute statistics;
SQL> select count(1) from big a where a.object_id in
(select b.object_id from small b);
COUNT(1)
----------
1600
已用时间: 00: 00: 00.09
Execution Plan
-------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=58
Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=58 Card=1600 Bytes=12800)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'SMALL' (Cost=2 Card=400 Bytes=1600)
5 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=53 Card=40000 Bytes=160000)
Statistics
-------------------------------------------------------
0 recursive calls
0 db block gets
543 consistent gets
0 physical reads
SQL> select count(1) from big a where exists
(select 1 from small b where a.object_id=b.object_id);
COUNT(1)
----------
1600
已用时间: 00: 00: 00.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=58 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=58 Card=1600 Bytes=12800)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'SMALL' (Cost=2 Card=400 Bytes=1600)
5 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=53 Card=40000 Bytes=160000)
Statistics
--------------------------------------------------------
0 recursive calls
0 db block gets
543 consistent gets
0 physical reads
SQL> select count(1) from small a where a.object_id in
(select b.object_id from big b);
COUNT(1)
----------
400
已用时间: 00: 00: 00.09
Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=56 Card=400 Bytes=3200)
3 2 TABLE ACCESS (FULL) OF 'SMALL' (Cost=2 Card=400 Bytes=1600)
4 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=53 Card=40000 Bytes=160000)
Statistics
------------------------------------------------------
0 recursive calls
0 db block gets
543 consistent gets
0 physical reads
SQL> select count(1) from small a where exists
(select 1 from big b where a.object_id=b.object_id);
COUNT(1)
----------
400
已用时间: 00: 00: 00.09
Execution Plan
-------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=56 Card=400 Bytes=3200)
3 2 TABLE ACCESS (FULL) OF 'SMALL' (Cost=2 Card=400 Bytes=1600)
4 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=53 Card=40000 Bytes=160000)
Statistics
-------------------------------------------------------
0 recursive calls
0 db block gets
543 consistent gets
0 physical reads
删除表分析,使用提示/*+ use_hash(a,b) */ 或者 /*+ hash_sj */,
两种语法都可以达到CBO的执行计划
SQL> analyze table big delete statistics;
SQL> analyze table small delete statistics;
SQL> select /*+ use_hash(a,b) */count(1) from big a where a.object_id in
(select b.object_id from small b);
SQL> select /*+ use_hash(a,b) */count(1) from big a where exists
(select 1 from small b where a.object_id=b.object_id);
SQL> select count(1) from small a where a.object_id in
(select /*+ hash_sj */ b.object_id from big b);
SQL> select count(1) from small a where exists
(select /*+ hash_sj */ 1 from big b where a.object_id=b.object_id);
下表列出了各种情况下的速度情况：
┌───────────┬──────────────────────────┬─────────────────────────┬─────────────┐
│ │ outer big,inner small │ outer small,inner big │ table rows │
├───────────┼──────────┬───────────────┼──────────┬──────────────┼─────────────┤
│ │ IN SQL │ EXISTS SQL │ IN SQL │ EXISTS SQL │ │
├───────────┼──────────┼───────────────┼──────────┼──────────────┼─────────────┤
│un-analyze │ 0.56s │ 3.10s │ 0.56s │ 0.25s │ big=40000 │
├───────────┼──────────┼───────────────┼──────────┼──────────────┤ and │
│ analyzed │ 0.09s │ 0.09s │ 0.09s │ 0.09s │ small=400 │
├───────────┼──────────┼───────────────┼──────────┼──────────────┼─────────────┤
│un-analyze │ 0.72s │ 3.53s │ 0.25s │ 2.97s │ big=5000 │
├───────────┼──────────┼───────────────┼──────────┼──────────────┤ and │
│ analyzed │ 0.09s │ 0.09s │ 0.09s │ 0.09s │ small=4000 │
└───────────┴──────────┴───────────────┴──────────┴──────────────┴─────────────┘
结论：在未对表进行分析前,若两个表数据量差异很大,则外层表是大表时使用IN较快,
外层表是小表时使用EXISTS较快;若两表数据量接近,则使用IN较快;
分析表后无论用IN还是EXISTS都变得更快,由于执行计划一样,所以速度一样;
所以：无论使用IN还是EXISTS,只要使用散列连接,即提示/*+ use_hash(a,b) */,
或者在子句中散列半连接提示/*+ hash_sj */, 就使其达到最优速度;
附注：半连接的提示有hash_sj、merge_sj、nl_sj
***********************************************************************************************************************
***********************************************************************************************************************
2、NOT IN 和 NOT EXISTS
NOT EXISTS的执行流程
select .....
from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);
可以理解为:
for x in ( select * from rollup )
loop
if ( not exists ( that query ) ) then
OUTPUT
end if;
end;
注意:NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。
对于not in 和 not exists的性能区别：
not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，
子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.
如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists，另外not in最好也可以用/*+ HASH_AJ */
或者外连接+is null，NOT IN 在基于成本的应用中较好
创建测试用例表big(40000 row)和small(1000 row)：
truncate table big;
truncate table small;
insert into big select * from dba_objects where rownum <=20000;
insert into big select * from dba_objects where rownum <=20000;
insert into small select * from dba_objects where rownum <=1000;
commit;
基本句型：
<1> not in
SQL> select count(1) from big a where a.object_id not in (select b.object_id from small b);
COUNT(1)
----------
38000
已用时间: 00: 00: 12.56
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'BIG'
4 2 TABLE ACCESS (FULL) OF 'SMALL'
Statistics
--------------------------------
分享到：
发表评论
-
oracle删除重复记录
2009-07-16 11:16 1215有困难，找猪八戒 Q：要删除一张表中的重复记录，但是要保留一条 ... -
db2 express-c 安装后检查及安装例子数据库
2009-02-27 17:07 3394摘自http://publib.boulder.ibm.com ... -
问：如何得到与WEB-INF同级目录下的配置文件
2008-09-23 08:35 2134Q: 有如下需求：需要从WEB-INF同级的目录下读取配 ... -
讨论如何优化这条sql
2008-09-11 16:33 1597SELECT * FROM ( ... -
N Vs Exist in SQL
2008-07-02 16:39 1393N Vs Exist in SQL 原文如下： http:// ... -
SQL 指南
2008-05-27 11:45 1047http://www.sql-tutorial.com/ -
orace 分析函数
2008-05-26 09:08 1212select x.num, sum(x.num) over ( ... -
oralce tutoial 指南
2008-03-22 14:21 1031http://www.exforsys.com/tutoria ... -
查找部分字段重复的记录 ORACLE Identifying duplicate rows
2008-03-13 08:49 1968http://www.jlcomp.demon.co.uk/f ... -
oracle 资源网站
2008-01-12 11:42 1877oracle alter table table_ ... -
Top 5 Oracle Reference Books 前5本 oracle 参考书
2008-01-12 11:24 1718http://databases.about.com/od/o ... -
Oracle与DB2、MySQL取前10条记录的对比<转＞
2008-01-11 16:46 2221原文：http://tech.ccidnet.com/art/ ... -
expert on e on one oracle - Thomas Kyte 读书笔记
2008-01-11 10:17 2111=============================== ... -
oracle 资源 整体理解oralce 比较好 英文网
2008-01-09 16:59 1193http://www.adp-gmbh.ch/ora/admi ... -
oracle java 插入 clob insert clob hibernate
2007-12-21 15:48 7230用jdbc 或者 hibernate http://www.w ... -
pl/sql 应用之一
2007-12-12 17:21 1138declare begin insert into x ... -
init.ora文件所在目录
2007-12-12 15:58 2167Oracle安装盘:\oracle\admin\DB名称\pf ... -
[Oracle] 如何解决ORA-04031 错误
2007-12-12 15:53 3254[Oracle] 如何解决ORA-04031 ... -
oracle faq 常见问题解答 http://www.orafaq.com/
2007-12-12 13:34 1499The Oracle FAQ http://www.oraf ... -
oracle 快速参考
2007-12-12 09:58 1095http://www.psoug.org/library.ht ...
相关推荐
SQL 中 IN、EXISTS、NOT IN、NOT EXISTS 的区别 IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符，它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符，...
在SQL查询中，`CASE WHEN`、`EXISTS`、`NOT EXISTS`以及`IN`和`NOT IN`是常用的操作符，它们用于处理复杂的条件判断和数据筛选。这些概念对于理解和编写高效的SQL语句至关重要，尤其是在数据分析和数据库管理中。 `...
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载！
### 经典SQL查询总结关于Exists, not Exists, IN, not IN 效率的说明 在数据库查询操作中，存在着多种方法来实现相似的功能，但不同的实现方式在性能上可能会有显著差异。本文将深入探讨 SQL 中 `EXISTS`, `NOT ...
- 效率：在某些情况下，`EXISTS` 和 `NOT EXISTS` 可能会提供相似的性能，但通常 `IN` 和 `NOT IN` 比较操作符在处理大量数据时更有效。然而，当涉及到子查询时，`EXISTS` 和 `NOT EXISTS` 可能更合适，因为它们只...
SQL语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句 SQL语句优化是数据库性能优化的重要方面之一。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS...
在SQL查询中，`IN`、`EXISTS`、`NOT IN`以及`NOT EXISTS`是四个常用的比较和过滤操作符。它们在处理数据时有不同的效率和适用场景，特别是涉及到大数据量时，优化这些操作符的使用对于提升数据库查询性能至关重要。 ...
exists 和 not exists的详细解释
MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论：exists查询过程：结论：not in与not existsnot in查询过程：结论：not exists查询过程：结论： 首先我们使用两个用户表作为实例 insert ...
在Oracle数据库中，`IN`、`EXISTS`、`NOT IN` 和 `NOT EXISTS` 是四个常用的子查询操作符，它们在SQL查询语句中扮演着不同的角色，且各有其性能特点。以下是对这些操作符的详细分析和比较。 1. `IN` 操作符： `IN` ...
在SQL查询语言中，`NOT EXISTS`、`NOT IN` 和 `NOT NULL` 是三种用于排除特定条件的数据记录的方法。然而，在实际应用中，这三种语法有着不同的应用场景和执行逻辑，有时初学者可能会误以为它们是等效的，但实际上...
相较于`IN`、`NOT IN`等操作，`EXISTS`与`NOT EXISTS`具有更高的效率，尤其是在处理大型数据集时。 #### EXISTS 介绍 `EXISTS`关键字用于检查子查询是否至少返回一行数据。如果子查询返回至少一行数据，则`EXISTS`...
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句2.txt，欢迎下载！
在SQL查询优化中，`IN`、`EXISTS`、`NOT IN`和`NOT EXISTS`是四个常见的比较和过滤条件，它们在不同场景下有不同的性能表现。以下是对这些操作符优化原则的详细说明： 1. **EXISTS的执行流程**： `EXISTS` 子查询...
在SQL查询中，`EXISTS`和`NOT EXISTS`是两个非常重要的子查询操作符，它们主要用于判断子查询是否返回结果。本篇文章将详细介绍这两个关键字的用法，并通过实例进行解析。 首先，`EXISTS`的语法是：主查询中的条件...
IN、EXISTS、NOT EXISTS、NOT IN 在 SQL 语句中的应用和区别 IN 语句和 EXISTS 语句都是 SQL 语句中用来判断是否存在某个值的语句，但是它们的实现机制和应用场景是不同的。 IN 语句是通过 hash 连接来实现的，它...
【标题】：“一次SQL Tuning引出来的not in , not exists 语句的N种写法2” 【描述】：文章并未直接提供描述，但从标题推测，该内容可能涉及数据库查询优化，特别是关于`not in`和`not exists`两种SQL查询子句的...