- 浏览: 1370608 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (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 1220有困难,找猪八戒 Q:要删除一张表中的重复记录,但是要保留一条 ... -
db2 express-c 安装后检查及安装例子数据库
2009-02-27 17:07 3403摘自http://publib.boulder.ibm.com ... -
问:如何得到与WEB-INF同级目录下的配置文件
2008-09-23 08:35 2147Q: 有如下需求:需要从WEB-INF同级的目录下读取配 ... -
讨论如何优化这条sql
2008-09-11 16:33 1603SELECT * FROM ( ... -
N Vs Exist in SQL
2008-07-02 16:39 1400N Vs Exist in SQL 原文如下: http:// ... -
SQL 指南
2008-05-27 11:45 1049http://www.sql-tutorial.com/ -
orace 分析函数
2008-05-26 09:08 1236select x.num, sum(x.num) over ( ... -
oralce tutoial 指南
2008-03-22 14:21 1045http://www.exforsys.com/tutoria ... -
查找部分字段重复的记录 ORACLE Identifying duplicate rows
2008-03-13 08:49 1973http://www.jlcomp.demon.co.uk/f ... -
oracle 资源网站
2008-01-12 11:42 1884oracle alter table table_ ... -
Top 5 Oracle Reference Books 前5本 oracle 参考书
2008-01-12 11:24 1743http://databases.about.com/od/o ... -
Oracle与DB2、MySQL取前10条记录的对比<转>
2008-01-11 16:46 2228原文:http://tech.ccidnet.com/art/ ... -
expert on e on one oracle - Thomas Kyte 读书笔记
2008-01-11 10:17 2116=============================== ... -
oracle 资源 整体理解oralce 比较好 英文网
2008-01-09 16:59 1213http://www.adp-gmbh.ch/ora/admi ... -
oracle java 插入 clob insert clob hibernate
2007-12-21 15:48 7243用jdbc 或者 hibernate http://www.w ... -
pl/sql 应用之一
2007-12-12 17:21 1155declare begin insert into x ... -
init.ora文件所在目录
2007-12-12 15:58 2193Oracle安装盘:\oracle\admin\DB名称\pf ... -
[Oracle] 如何解决ORA-04031 错误
2007-12-12 15:53 3264[Oracle] 如何解决ORA-04031 ... -
oracle faq 常见问题解答 http://www.orafaq.com/
2007-12-12 13:34 1502The Oracle FAQ http://www.oraf ... -
oracle 快速参考
2007-12-12 09:58 1103http://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查询子句的...