- 浏览: 373569 次
- 性别:
- 来自: 青岛
文章分类
最新评论
-
lippeng:
楼主,你好!这篇中提到的一个话题,是我现在非常关心的,我自己还 ...
使用JUnit测试通过 HttpClient(https协议)访问支付宝接口时不能自动获得证书的解决办法 -
snoopy7713:
[2014-03-14 17:55:06.651] TCP ...
刚完成Struts的Virgo插件,分享一下设计思路和Virgo OSGi内部的独特机制 -
snoopy7713:
需要看一下,你的代码说的挺模糊的。我的联系方式QQ 16200 ...
刚完成Struts的Virgo插件,分享一下设计思路和Virgo OSGi内部的独特机制 -
roronjavaeye:
不错,受教了
java_class反编译后的代码还原 -
daoyongyu:
讲的很好,很详细。
Struts2配置文件详解——struts.properties
经常别人说EXISTS比IN快!NOT EXISTS比NOT IN快!然而事实真的如此么?
我们先讨论IN和EXISTS。
select * from t1 where x in ( select y from t2 )
事实上可以理解为:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
——如果你有一定的SQL优化经验,从这句很自然的可以想到t2绝对不能是个大表,因为需要对t2进行全表的“唯一排序”,如果t2很大这个排序的性能是不可忍受的。但是t1可以很大,为什么呢?最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想,如果t1.x和t2.y都有索引,我们知道索引是种有序的结构,因此t1和t2之间最佳的方案是走merge join。另外,如果t2.y上有索引,对t2的排序性能也有很大提高。
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
——这个更容易理解,t1永远是个表扫描!因此t1绝对不能是个大表,而t2可以很大,因为y=x.x可以走t2.y的索引。
综合以上对IN/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
如果你对上述说法表示怀疑,请看以下测试:
********************************************************************************
SQL> create table big as select * from all_objects;
表已创建。
SQL> insert /*+ append */ into big select * from big;
已创建26872行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into big select * from big;
已创建53744行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into big select * from big;
已创建107488行。
SQL> commit;
提交完成。
SQL> create index big_idx on big(object_id);
索引已创建。
SQL> create table small as select * from all_objects where rownum < 100;
表已创建。
SQL> create index small_idx on small(object_id);
索引已创建。
********************************************************************************
运行SQL并设置EVENT=10046,用TKPROF格式化TRACE文件,结果如下。
大表在外,小表在内的测试:
********************************************************************************
select count(subobject_name)
from big
where object_id in ( select object_id from small )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.14 29 900 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.15 29 900 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 792 TABLE ACCESS (BY INDEX ROWID) OF 'BIG' 892 NESTED LOOPS 99 VIEW OF 'VW_NSO_1' 99 SORT (UNIQUE) 99 TABLE ACCESS (FULL) OF 'SMALL' 792 INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)
select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.90 2.72 2917 216125 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.90 2.72 2917 216125 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 792 FILTER 214976 TABLE ACCESS (FULL) OF 'BIG' 225 INDEX (RANGE SCAN) OF 'SMALL_IDX' (NON-UNIQUE) ******************************************************************************** 用IN的性能数据: cpu=0.00 elapsed=0.15 query=900 current=0 disk=29 用EXISTS的性能数据: cpu=1.90 elapsed=2.72 query=216125 current=0 disk=2917 ——在CPU的消耗和LIO、PIO上的对比十分明显,IN的效率高得多!
大表在内,小表在外的测试:
********************************************************************************
select count(subobject_name)
from small
where object_id in ( select object_id from big )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.41 1.71 2917 2982 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.41 1.72 2917 2982 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 99 TABLE ACCESS (BY INDEX ROWID) OF 'SMALL' 26972 NESTED LOOPS 26872 VIEW OF 'VW_NSO_1' 26872 SORT (UNIQUE) 214976 TABLE ACCESS (FULL) OF 'BIG' 99 INDEX (RANGE SCAN) OF 'SMALL_IDX' (NON-UNIQUE)
select count(subobject_name)
from small
where exists ( select null from big where small.object_id = big.object_id )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 202 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 202 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 99 FILTER 99 TABLE ACCESS (FULL) OF 'SMALL' 99 INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE) ******************************************************************************** 用IN的性能数据: cpu=0.41 elapsed=1.72 query=2982 current=26 disk=2917 用EXISTS的性能数据: cpu=0.00 elapsed=0.00 query=202 current=0 disk=0 ——在CPU的消耗和PIO、LIO上的对比十分明显,EXISTS效率高得多!
有些遗憾的是我这个测试是在RBO下进行的,RBO是个死板的只根据优先级来确定执行计划的优化器,RBO不会评估实际的执行计划对系统造成的影响。在RBO中NESTED LOOP的优先级要远远大于MERGE JOIN,只要能走NESTED LOOP RBO就绝不会走MERGE JOIN。如果你用的是CBO,并且对表、索引做过统计分析,上面IN的测试一定会选择走MERGE JOIN。我们用HINTS在RBO下强制走MERGE JOIN对比一下这个SQL分别走MJ和NL的性能:
********************************************************************************
select count(subobject_name)
from small
where object_id in ( select/*+ use_merge(small big) */ object_id from big )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.17 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.09 0.27 187 473 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.10 0.44 187 473 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 99 MERGE JOIN 26872 SORT (UNIQUE) 214976 INDEX (FULL SCAN) OF 'BIG_IDX' (NON-UNIQUE) 99 SORT (JOIN) 99 TABLE ACCESS (FULL) OF 'SMALL' ******************************************************************************** 可以看到: NESTED LOOP:cpu=0.41 elapsed=1.72 query=2982 current=26 disk=2917 MERGE JOIN:cpu=0.10 elapsed=0.44 query=437 current=2 disk=187 ——这也证实了我上面的说法。很多人不敢让自己的SQL走merge join,其实对于两个已经具有排序结构的表merge join是最佳选择。
下面我们讨论NOT IN和NOT EXISTS,我把它们放在一起讨论实属被逼无奈,因为很多人喜欢拿它们比较。其实NOT IN/NOT EXISTS与IN/EXISTS不一样,IN/EXISTS是完全可以作为等价替换结构的,而NOT IN/NOT EXISTS则不同,它们并不是等价替换结构!只有当子查询中不可能返回空值时,NOT IN/NOT EXISTS才可以等价替换。
为什么?请看:
********************************************************************************
SQL> conn scott/tiger@tdb;
已连接。
SQL> select count(*)
2 from emp
3 where mgr is null;
COUNT(*)
----------------
1
SQL> select count(*)
2 from emp
3 where empno not in ( select mgr from emp );
COUNT(*)
----------------
0
SQL> select count(*)
2 from emp t1
3 where not exists ( select null
4 from emp t2
5 where t2.mgr = t1.empno );
COUNT(*)
----------------
7
********************************************************************************
如果子查询中返回的结果集含有空值NOT IN永远是0,因为NULL代表“未知”,任何值和NULL比较永远是false。
现在我们基于假设——子查询中不返回空值,来比较NOT IN和NOT EXISTS。
在RBO中如果不使用HINTS来改变NOT IN的执行计划,几乎任何时候NOT IN都比NOT EXISTS慢得多,在CBO中如果具有准确的统计信息NOT IN的效率和NOT EXISTS的一样,甚至会比NOT EXISTS快得多。
调整NOT IN性能的基本原则是:如果想让NOT IN跑得快就必须走合适的连接。
select * from t1 where x not in ( select y from t2 )
以这个句子为例(y无空值)
这个句子可以等价替换为:
a) select * from t1 where not exists ( select null from t2 where t2.y=t1.x)
或
b) select t1.* from t1, t2 where t1.x=t2.y(+) and t2.y is null
测试如下:
********************************************************************************
SQL> create table t1 as select * from all_objects where rownum <= 5000;
表已创建。
SQL> create table t2 as select * from all_objects where rownum <= 4950;
表已创建。
SQL> create index t2_idx on t2(object_id);
索引已创建。
********************************************************************************
RBO下的测试:
********************************************************************************
select count(*)
from t1 rbo
where object_id not in ( select object_id from t2 )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 6.13 19.12 127487 197502 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 6.13 19.13 127487 197502 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 50 FILTER 5000 TABLE ACCESS (FULL) OF 'T1' 4950 TABLE ACCESS (FULL) OF 'T2'
select count(*)
from t1 rbo
where not exists ( select null from t2 where t2.object_id = rbo.object_id)
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.12 83 10075 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.02 0.12 83 10075 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 50 FILTER 5000 TABLE ACCESS (FULL) OF 'T1' 4950 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE)
select count(*)
from t1, t2 rbo
where t1.object_id = rbo.object_id(+) and rbo.object_id is null
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.05 72 5087 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.06 72 5087 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 50 FILTER 5000 NESTED LOOPS (OUTER) 5000 TABLE ACCESS (FULL) OF 'T1' 4950 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) ******************************************************************************** RBO自己选择的执行计划,性能数据: NOT IN:cpu=6.13 elapsed=19.13 query=197502 current=0 disk=127487 NOT EXISTS:cpu=0.02 elapsed=0.12 query=10075 current=0 disk=83 OUTER JOIN:cpu=0.00 elapsed=0.06 query=5087 current=0 disk=72 ——NOT EXISTS的效率比NOT IN好很多,但与OUTER JOIN相比NOT EXISTS的效率略低。
RBO,用HINTS改变NOT IN的执行计划:
********************************************************************************
select count(*)
from t1 rbo
where object_id not in ( select/*+ hash_aj(rbo t2) */ object_id from t2 )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.04 0.45 0 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.09 48 191 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.05 0.55 48 194 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (AGGREGATE) 0 HASH JOIN (ANTI) 0 TABLE ACCESS (FULL) OF 'T1' 0 INDEX (FAST FULL SCAN) OF 'T2_IDX' (NON-UNIQUE) ******************************************************************************** 在只有t2.object_id有索引的情况下,hash join-anti性能数据如下: HJ-ANTI:cpu=0.05 elapsed=0.55 query=194 current=0 disk=48 ——性能好了很多!
在t1.object_id上建立索引,使用merge join-anti:
********************************************************************************
select count(*)
from t1 rbo
where object_id not in ( select /*+ merge_aj(rbo t2) */ object_id from t2 )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 28 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 28 0 1
Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 50 MERGE JOIN (ANTI) 5000 INDEX (FULL SCAN) OF 'T1_IDX' (NON-UNIQUE) 4950 SORT (UNIQUE) 4950 INDEX (FAST FULL SCAN) OF 'T2_IDX' (NON-UNIQUE) ******************************************************************************** 在t1.object_id上建立索引,merge join-anti的性能数据如下: MJ-ANTI:cpu=0.00 elapsed=0.00 query=28 current=0 disk=0 ——这个NOT IN语句在t1.object_id、t2.object_id都有索引的情况下,merge join-anti的效率高于上面的任何SQL。
综上,只要NOT IN走合适的连接,其效率很高甚至高于NOT EXISTS和OUTER JOIN。
发表评论
-
复杂的视图
2011-09-13 14:56 845CREATE OR REPLACE VIEW ECC_FST. ... -
ORACLE跨数据库查询
2011-03-10 10:33 1169本文简述了通过创建database link实现ORACL ... -
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME 错误的解决
2010-12-29 10:14 11831. 打开<OracleHome>/network ... -
用户管理
2010-12-27 12:52 956创建用户 create user xxxx ident ... -
oracle 修改数据库的字符集编码为UTF-8
2010-12-27 12:01 50131、查看数据库字符集 ?数据库服务器字符集select * f ... -
PLSQL Developer 8 连接Oracle 11g X64版
2010-12-20 23:26 3965我的机器是Windows 7 Enterp ... -
AWR快速入门
2010-12-15 15:13 995一、WHY——为什么会出现ASH和AWR? 1. 1 ... -
为何要分析数据字典
2010-12-14 09:48 918客户现场总是有人抱怨O ... -
Oracle学习笔记:数据字典
2010-12-14 09:46 962oracle数据字典 说起字典,下面让我来打个比方。我们 ... -
java调用oracle存储过程返回多条记录(结果集)
2010-10-25 10:43 2292游标,类型,嵌套表,存储包,存储过程 --定义TYPE ... -
Oracle数据导入导出imp/exp命令和grant命令
2010-08-18 22:46 1915一、数据导入导出命令 Oracle数据导入导出imp/ ... -
plSQL技巧:“tns:无法解析指定的连接标识符”问题详解
2010-05-25 15:13 2593tns:监听服务Transparance Netwo ... -
简单配置 listener.ora tnsname.ora 以及 sqlnet.ora 说明
2010-05-25 15:03 3714今天弄了一个实验 简单的配置了一下一个DBLINK 我 ... -
ORACLE LISTENER 详解
2010-05-25 14:58 1543# listener.ora Network Configur ...
相关推荐
### 经典SQL查询总结关于Exists, not Exists, IN, not IN 效率的说明 在数据库查询操作中,存在着多种方法来实现相似的功能,但不同的实现方式在性能上可能会有显著差异。本文将深入探讨 SQL 中 `EXISTS`, `NOT ...
很明显使用NOT EXISTS的效率高多了。 使用EXISTS和NOT EXISTS的优点 使用EXISTS和NOT EXISTS可以提高查询的效率,避免了使用NOT IN和IN的低效率。同时,EXISTS和NOT EXISTS也可以使查询语句变得更加简洁和易于理解...
相较于`IN`、`NOT IN`等操作,`EXISTS`与`NOT EXISTS`具有更高的效率,尤其是在处理大型数据集时。 #### EXISTS 介绍 `EXISTS`关键字用于检查子查询是否至少返回一行数据。如果子查询返回至少一行数据,则`EXISTS`...
`NOT EXISTS`在这里的作用是,对于每个`t1.id`,如果子查询找不到比`t1.c_date`更晚的记录,则返回该记录。 **示例4**: ```sql SELECT distinct a.id, a.name FROM a, b WHERE a.id = b.a_id ``` 与: ```sql ...
MySQL中的`NOT IN`, `LEFT JOIN`, `IS NULL`, 和 `NOT EXISTS` 是四种不同的SQL查询方式,它们在特定情况下可以实现相似的功能,但实际执行效率可能会有很大差异。本文主要探讨这四种方法在处理大数据量时的性能表现...
因此,在大多数情况下,NOT EXISTS 语句的效率比 NOT IN 语句高。 例如,如果我们有两个表 A 和 B,我们可以使用以下语句: SELECT * FROM A WHERE cc NOT IN (SELECT cc FROM B) 这条语句的效率可能不高,因为它...
- 当主表(T1)数据量远小于子表(T2)时,使用 `EXISTS` 效率更高。这是因为 `EXISTS` 只需找到一个匹配项即可停止处理,而 `IN` 需要处理整个子查询结果集。 - 当主表数据量远大于子表时,使用 `IN` 更高效。此时...
3. 优先考虑`EXISTS`:在比较多个潜在查询方法时,`EXISTS`往往比其他方法(如`IN`、`NOT IN`)更快,特别是在大型数据集上。 六、实际应用示例 假设我们有两个表,`employees`和`departments`,我们想找出没有部门...
在SQL查询中,`IN`、`INNER JOIN`、`OUTER JOIN` 和 `EXISTS` 是四个重要的关键字,它们用于处理数据表之间的关联和筛选。这些概念在数据库设计和数据检索中至关重要,理解并熟练运用它们能显著提高查询效率。 1. *...
在MySQL中,当我们需要向...总结,使用`NOT EXISTS`子句可以在确保不插入重复记录的同时,保持较高的查询效率。在实际应用中,应根据具体需求和数据结构选择最合适的防止插入重复记录的方法,并注意优化查询性能。
`NOT EXISTS` 可以利用索引进行优化,因为它仅需知道子查询是否有匹配项,一旦找到,就不再继续检查其他行,因此在大多数情况下,对于大数据集,`NOT EXISTS` 的效率较高。 2. **IN 子句** `IN` 子句用于判断某个...
Exists 语句的效率取决于主查询和子查询的大小,如果主查询的结果集小于子查询的结果集,那么 EXISTS 语句的效率将很高。反之,如果主查询的结果集大于子查询的结果集,那么 IN 语句的效率将更高。 在实际应用中,...
在处理大量数据时,尤其需要注意避免使用效率较低的操作符,如`IN`和`NOT IN`。这些操作符虽然在编写时提供了简洁和易读性,但在执行效率上往往不如其他替代方法。本文将深入探讨`IN`和`NOT IN`的替代方案,并通过...
在10g中,这个查询的`buffer get`(缓冲区获取)较低,而在12c中较高,这表明了`NOT EXISTS`子句对外层查询性能的影响。 在10g中,由于`t2`中有`dep_id`为'mm'的记录,`NOT EXISTS`子句在匹配过程中可以更快地确定...
反之,如果B表的记录数远大于A表,`EXISTS` 的效率更高,因为它避免了大量不必要的比较。然而,这并不是绝对的,实际性能还取决于表结构、索引的存在以及数据库的优化策略。 在插入记录时,为了避免插入重复数据,...
通过合理处理NULL值、精确使用比较运算符、明智选择LIKE语句、谨慎使用`ORDER BY`子句以及优选`NOT EXISTS`而非`NOT IN`,可以显著提升查询效率,从而改善整体系统性能。在实践中,持续监控和调整查询策略,结合索引...
而`NOT EXISTS`仍然可以利用索引来优化查询,因此在大多数情况下,`NOT EXISTS`的效率更高。 4. **特殊情况** - **第一种情况**:如果外层表有索引,且内层表较小,`IN`使用外层表的索引,效率较高。 - **第二种...
- 结合使用`IN`和`NOT IN`可以精确控制查询条件,如`SELECT * FROM tb_name WHERE id IN (10,12,15,16) AND NOT id IN (21,22,23)`,这将返回id在第一个列表但不在第二个列表的记录。 2. `EXISTS/NOT EXISTS`: -...