来源：蜘蛛抓取(WebSpider)
时间：2016-08-28 03:30
标签：
not in not exists
在oracle中not exists与not in 两种用法,哪一种效率高一点
潘a多a拉0071
不是绝对的，子查询记录少的话not in高，否则用not exists
楼上正解，不过在实际的生产环境下，还是使用not exsits会多一点
为您推荐：
扫描下载二维码oracle 中 in 和exists用法区别_百度知道36583人阅读
数据库（20）
IN:确定给定的值是否与子查询或列表中的值相匹配。
IN 关键字使您得以选择与列表中的任意一个值匹配的行。
SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERECategoryID IN (1, 4, 5)
IN 关键字之后的项目必须用逗号隔开，并且括在括号中。
NOT IN:通过 NOT IN关键字引入的子查询也返回一列零值或更多值。
以下查询查找没有出版过商业书籍的出版商的名称。
SELECTpub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHEREtype = 'business')
使用 EXISTS和 NOT EXISTS引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。
差集包含只属于两个集合中的第一个集合的元素。
EXISTS:指定一个子查询，检测行的存在。
SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT *FROM titles WHERE pub_id = publishers.pub_id AND type =
'business')
SELECTdistinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titlesWHERE type = 'business')
&&&&&&&&&&&&&& 两者的区别:
EXISTS:后面可以是整句的查询语句如:SELECT *FROM titles
IN:后面只能是对单列:SELECTpub_id FROM titles
NOT EXISTS:
例如，要查找不出版商业书籍的出版商的名称：
SELECTpub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id =publishers.pub_id AND type =
'business')
下面的查询查找已经不销售的书的名称:
SELECT titleFROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id =titles.title_id)
//验证有发布产品的公司个数
select id,name from company where exists (select * from product where comid=company.id)
select id,name from company where id in(select comid from product)
//验证没有发布产品的公司个数
select id,name from company where not exists (select * from product where comid=company.id)
select id,name from company where id not in(select comid from product)
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1014211次
积分：9548
积分：9548
排名：第1248名
原创：180篇
转载：129篇
评论：154条
(2)(8)(6)(3)(1)(3)(1)(2)(5)(2)(9)(11)(8)(4)(4)(18)(24)(1)(19)(4)(2)(3)(11)(14)(2)(7)(18)(4)(5)(11)(5)(6)(46)(7)(20)(13)<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
您的访问请求被拒绝 403 Forbidden - ITeye技术社区
您的访问请求被拒绝
亲爱的会员，您的IP地址所在网段被ITeye拒绝服务，这可能是以下两种情况导致：
一、您所在的网段内有网络爬虫大量抓取ITeye网页，为保证其他人流畅的访问ITeye，该网段被ITeye拒绝
二、您通过某个代理服务器访问ITeye网站，该代理服务器被网络爬虫利用，大量抓取ITeye网页
请您点击按钮解除封锁&oracle in和exists、not in和not exists原理和性能探究
in和exists、not in和not exists原理和性能探究
& & & 对于in和exists、not in和not exists还是有很多的人有疑惑，更有甚者禁用not in，所有的地方都要用not exists，它真的高效吗？通过下面的使用我们来证明。
先制造一些数据
SQL& drop table test1
SQL& drop table test2
SQL& create table test1 as select * from dba_objects where rownum &=1000;
SQL& create table test2 as select * from dba_
SQL& exec dbms_stats.gather_table_stats(user,'test1');
SQL& exec dbms_stats.gather_table_stats(user,'test2');
SQL& set autotrace traceonly
in和exists原理及性能实验：
SQL& select * from test1 t1 where t1.object_id in (select t2.object_id from test2 t2);
已选择1000行。
----------------------------------------------------------
Plan hash value:
----------------------------------------------------------------------------
| Id &| Operation & & & & &| Name &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & | & & & | & 997 | 84745 | & 168 & (3)| 00:00:03 |
|* &1 | &HASH JOIN SEMI & &| & & & | & 997 | 84745 | & 168 & (3)| 00:00:03 |
| & 2 | & TABLE ACCESS FULL| TEST1 | &1000 | 80000 | & & 5 & (0)| 00:00:01 |
| & 3 | & TABLE ACCESS FULL| TEST2 | 50687 | & 247K| & 162 & (2)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - access(&T1&.&OBJECT_ID&=&T2&.&OBJECT_ID&)
----------------------------------------------------------
& & & & & 1 &recursive calls
& & & & & 0 &db block gets
& & & & &95 &consistent gets
& & & & & 0 &physical reads
& & & & & 0 &redo size
& & & 45820 &bytes sent via SQL*Net to client
& & & &1111 &bytes received via SQL*Net from client
& & & & &68 &SQL*Net roundtrips to/from client
& & & & & 0 &sorts (memory)
& & & & & 0 &sorts (disk)
& & & &1000 &rows processed
SQL& select * &from test1 t1
& 2 & where exists (select 1 from test2 t2 where t1.object_id = t2.object_id);
已选择1000行。
----------------------------------------------------------
Plan hash value:
----------------------------------------------------------------------------
| Id &| Operation & & & & &| Name &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & | & & & | & 997 | 84745 | & 168 & (3)| 00:00:03 |
|* &1 | &HASH JOIN SEMI & &| & & & | & 997 | 84745 | & 168 & (3)| 00:00:03 |
| & 2 | & TABLE ACCESS FULL| TEST1 | &1000 | 80000 | & & 5 & (0)| 00:00:01 |
| & 3 | & TABLE ACCESS FULL| TEST2 | 50687 | & 247K| & 162 & (2)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - access(&T1&.&OBJECT_ID&=&T2&.&OBJECT_ID&)
----------------------------------------------------------
& & & & & 0 &recursive calls
& & & & & 0 &db block gets
& & & & &95 &consistent gets
& & & & & 0 &physical reads
& & & & & 0 &redo size
& & & 45820 &bytes sent via SQL*Net to client
& & & &1111 &bytes received via SQL*Net from client
& & & & &68 &SQL*Net roundtrips to/from client
& & & & & 0 &sorts (memory)
& & & & & 0 &sorts (disk)
& & & &1000 &rows processed
& & & & 结论：在 10g中,in 和 exists其实是一样的，原理就是两张表做HASH JOIN SEMI。也可以通过10053事件看到两条sql语句最终转换成同一条sql。
not in和not exists原理及性能实验：
not exists 比 not in效率高的例子
SQL& select count(*) from test1 where object_id not in(select object_id from test2);
----------------------------------------------------------
Plan hash value:
-----------------------------------------------------------------------------
| Id &| Operation & & & & & | Name &| Rows &| Bytes | Cost (%CPU)| Time & & |
-----------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & &| & & & | & & 1 | & & 4 | 81076 & (2)| 00:16:13 |
| & 1 | &SORT AGGREGATE & & | & & & | & & 1 | & & 4 | & & & & & &| & & & & &|
|* &2 | & FILTER & & & & & &| & & & | & & & | & & & | & & & & & &| & & & & &|
| & 3 | & &TABLE ACCESS FULL| TEST1 | &1000 | &4000 | & & 5 & (0)| 00:00:01 |
|* &4 | & &TABLE ACCESS FULL| TEST2 | & & 1 | & & 5 | & 162 & (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM &TEST2& &TEST2& WHERE
& & & & & & & LNNVL(&OBJECT_ID&&&:B1)))
& &4 - filter(LNNVL(&OBJECT_ID&&&:B1))
----------------------------------------------------------
& & & & & 1 &recursive calls
& & & & & 0 &db block gets
& & & &9410 &consistent gets
& & & & & 0 &physical reads
& & & & & 0 &redo size
& & & & 407 &bytes sent via SQL*Net to client
& & & & 385 &bytes received via SQL*Net from client
& & & & & 2 &SQL*Net roundtrips to/from client
& & & & & 0 &sorts (memory)
& & & & & 0 &sorts (disk)
& & & & & 1 &rows processed
SQL& select count(*) from test1 t1 where not exists
& & (select 1 from test2 t2 where t1.object_id=t2.object_id);
----------------------------------------------------------
Plan hash value:
-----------------------------------------------------------------------------
| Id &| Operation & & & & & | Name &| Rows &| Bytes | Cost (%CPU)| Time & & |
-----------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & &| & & & | & & 1 | & & 9 | & 168 & (3)| 00:00:03 |
| & 1 | &SORT AGGREGATE & & | & & & | & & 1 | & & 9 | & & & & & &| & & & & &|
|* &2 | & HASH JOIN ANTI & &| & & & | & & 3 | & &27 | & 168 & (3)| 00:00:03 |
| & 3 | & &TABLE ACCESS FULL| TEST1 | &1000 | &4000 | & & 5 & (0)| 00:00:01 |
| & 4 | & &TABLE ACCESS FULL| TEST2 | 50687 | & 247K| & 162 & (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &2 - access(&T1&.&OBJECT_ID&=&T2&.&OBJECT_ID&)
----------------------------------------------------------
& & & & & 1 &recursive calls
& & & & & 0 &db block gets
& & & & 717 &consistent gets
& & & & & 0 &physical reads
& & & & & 0 &redo size
& & & & 407 &bytes sent via SQL*Net to client
& & & & 385 &bytes received via SQL*Net from client
& & & & & 2 &SQL*Net roundtrips to/from client
& & & & & 0 &sorts (memory)
& & & & & 0 &sorts (disk)
& & & & & 1 &rows processed
& & & & &&
not in比not exists 效率高的例子
SQL& Set autotrace off
SQL& drop table test1
表已删除。
SQL& drop table test2
表已删除。
SQL& create table test1 as select * from dba_objects where rownum &=5;
表已创建。
SQL& create table test2 as select * from dba_
表已创建。
SQL& Insert into test2 select * from dba_
已创建50687行。
SQL& Insert into test2 select * from test2;
已创建101374行。
SQL& Insert into test2 select * from test2;
已创建202748行。
提交完成。
SQL& exec dbms_stats.gather_table_stats(user,'test1');
PL/SQL 过程已成功完成。
SQL& exec dbms_stats.gather_table_stats(user,'test2');
PL/SQL 过程已成功完成。
SQL& Set autotrace traceonly
SQL& select count(*) from test1 where object_id not in(select object_id from test2);
----------------------------------------------------------
Plan hash value:
-----------------------------------------------------------------------------
| Id &| Operation & & & & & | Name &| Rows &| Bytes | Cost (%CPU)| Time & & |
-----------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & &| & & & | & & 1 | & & 3 | &3143 & (2)| 00:00:38 |
| & 1 | &SORT AGGREGATE & & | & & & | & & 1 | & & 3 | & & & & & &| & & & & &|
|* &2 | & FILTER & & & & & &| & & & | & & & | & & & | & & & & & &| & & & & &|
| & 3 | & &TABLE ACCESS FULL| TEST1 | & & 5 | & &15 | & & 3 & (0)| 00:00:01 |
|* &4 | & &TABLE ACCESS FULL| TEST2 | & & 8 | & &40 | &1256 & (2)| 00:00:16 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM &TEST2& &TEST2& WHERE
& & & & & & & LNNVL(&OBJECT_ID&&&:B1)))
& &4 - filter(LNNVL(&OBJECT_ID&&&:B1))
----------------------------------------------------------
& & & & & 1 &recursive calls
& & & & & 0 &db block gets
& & & & &23 &consistent gets
& & & & & 0 &physical reads
& & & & & 0 &redo size
& & & & 407 &bytes sent via SQL*Net to client
& & & & 385 &bytes received via SQL*Net from client
& & & & & 2 &SQL*Net roundtrips to/from client
& & & & & 0 &sorts (memory)
& & & & & 0 &sorts (disk)
& & & & & 1 &rows processed
SQL& select count(*) from test1 t1 where not exists
& & (select 1 from test2 t2 where t1.object_id=t2.object_id);
----------------------------------------------------------
Plan hash value:
-----------------------------------------------------------------------------
| Id &| Operation & & & & & | Name &| Rows &| Bytes | Cost (%CPU)| Time & & |
-----------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & &| & & & | & & 1 | & & 8 | &1263 & (3)| 00:00:16 |
| & 1 | &SORT AGGREGATE & & | & & & | & & 1 | & & 8 | & & & & & &| & & & & &|
|* &2 | & HASH JOIN ANTI & &| & & & | & & 1 | & & 8 | &1263 & (3)| 00:00:16 |
| & 3 | & &TABLE ACCESS FULL| TEST1 | & & 5 | & &15 | & & 3 & (0)| 00:00:01 |
| & 4 | & &TABLE ACCESS FULL| TEST2 | & 405K| &1981K| &1253 & (2)| 00:00:16 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &2 - access(&T1&.&OBJECT_ID&=&T2&.&OBJECT_ID&)
----------------------------------------------------------
& & & & & 1 &recursive calls
& & & & & 0 &db block gets
& & & &5609 &consistent gets
& & & & & 0 &physical reads
& & & & & 0 &redo size
& & & & 407 &bytes sent via SQL*Net to client
& & & & 385 &bytes received via SQL*Net from client
& & & & & 2 &SQL*Net roundtrips to/from client
& & & & & 0 &sorts (memory)
& & & & & 0 &sorts (disk)
& & & & & 1 &rows processed
& & & & 结论：not in 和not exists原理是nestedloops 与HASH JOIN的区别，not in中的filter算法类似于nestedloops。如果比较两者的性能，就是比较nestedloops 与HASH JOIN的性能差异。在本例子中：
& & not in 性能 大于not exists &test1的数据量5条，test2数量40多万条。
& & not exists 性能 大于not in &test1的数据量1000条，test2数量50687条。
顶一下(0) 踩一下(0)
热门标签：
