再探not in 与not exists
来源：互联网 发布：淘宝拍a发b群 编辑：程序博客网 时间：2024/04/27 23:02
有些公司在SQL规范中写到，禁止使用not in，所有用not in的地方都要用not exists，这种结论对吗？下面我们来做实验：
1. 准备环境
C:\Documents and Settings\guogang>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 11月 11 19:54:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table test1 purge;
SQL> drop table test2 purge;
SQL> create table test1 as select * from dba_objects where rownum <=1000;
SQL> create table test2 as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats(user,'test1');
SQL> exec dbms_stats.gather_table_stats(user,'test2');
2. not exist 比not in效率高的场景
SQL> select count(*) from test1 where object_id not in(select object_id from test2);
执行计划
----------------------------------------------------------
Plan hash value: 3641219899
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 73799 (1)| 00:16:22 |
| 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 | 148 (1)| 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))
统计信息
----------------------------------------------------------
0 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: 240185659
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 153 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN ANTI | | 6 | 54 | 153 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL| TEST1 | 1000 | 4000 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST2 | 50507 | 246K| 148 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
714 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
3. not in比not exist的效率高的场景
SQL> Set autotrace off
SQL> drop table test1 purge;
SQL> drop table test2 purge;
SQL> create table test1 as select * from dba_objects where rownum <=5;
SQL> create table test2 as select * from dba_objects;
SQL> Insert into test2 select * from dba_objects;
SQL> Insert into test2 select * from test2;
SQL> Insert into test2 select * from test2;
SQL> Commit;
SQL> exec dbms_stats.gather_table_stats(user,'test1');
SQL> exec dbms_stats.gather_table_stats(user,'test2');
SQL> Set autotrace traceonly
SQL> select count(*) from test1 where object_id not in(select object_id from test2);
执行计划
----------------------------------------------------------
Plan hash value: 3641219899
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2845 (1)| 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 | 1137 (1)| 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: 240185659
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1141 (1)| 00:00:16 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN ANTI | | 1 | 8 | 1141 (1)| 00:00:16 |
| 3 | TABLE ACCESS FULL| TEST1 | 5 | 15 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST2 | 402K| 1965K| 1136 (1)| 00:00:16 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5547 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
4. 当test1中关联字段有null值时，not in返回结果为空
SQL> set autotrace off
SQL> Update test1 set object_id = null where rownum <10;
SQL> Commit;
SQL> select count(*) from test2 t2 where t2.object_id not in(select t1.object_id from test1 t1);
COUNT(*)
----------
0
SQL> select count(*) from test2 t2 where not exists
(select 1 from test1 t1 where t1.object_id=t2.object_id);
COUNT(*)
----------
404056
5. not in和 not exists如何能一样
在oracle 10g的环境下，可以看到not in经过改写后，执行计划一样，查出来的数据量也一样。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>select count(*) from test2 where object_id not in(select t1.object_id from test1 t1 where
T1.object_id is not null) and object_id is not null;
COUNT(*)
----------
404056
SQL> select count(*) from test2 t1 where not exists
(select 1 from test1 t2 where t1.object_id=t2.object_id);
COUNT(*)
----------
404056
SQL> set autotrace traceonly
SQL> select count(*) from test2 where object_id not in(select t1.object_id from test1 t1 where
T1.object_id is not null) and object_id is not null;
执行计划
----------------------------------------------------------
Plan hash value: 2788255037
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1141 (1)| 00:00:16 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN RIGHT ANTI| | 402K| 3145K| 1141 (1)| 00:00:16 |
|* 3 | TABLE ACCESS FULL | TEST1 | 5 | 15 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEST2 | 402K| 1965K| 1136 (1)| 00:00:16 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="T1"."OBJECT_ID")
3 - filter("T1"."OBJECT_ID" IS NOT NULL)
4 - filter("OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5547 consistent gets
0 physical reads
0 redo size
410 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 test2 t1 where not exists
(select 1 from test1 t2 where t1.object_id=t2.object_id);
执行计划
----------------------------------------------------------
Plan hash value: 2788255037
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1141 (1)| 00:00:16 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN RIGHT ANTI| | 402K| 3145K| 1141 (1)| 00:00:16 |
| 3 | TABLE ACCESS FULL | TEST1 | 5 | 15 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST2 | 402K| 1965K| 1136 (1)| 00:00:16 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5547 consistent gets
0 physical reads
0 redo size
410 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
在oracle 11g下当test1中没有null那就完全一样了,当test1中有null后not in的结果还是不一样。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table test1 as select * from dba_objects where rownum <=1000;
SQL> create table test2 as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats(user,'test1');
SQL> exec dbms_stats.gather_table_stats(user,'test2');
SQL> select count(*) from test2 where object_id not in(select object_id from test1);
COUNT(*)
----------
71226
SQL> select count(*) from test2 t1 where not exists
(select 1 from test1 t2 where t1.object_id=t2.object_id);
COUNT(*)
----------
71226
SQL> set autotrace traceonly
SQL> select count(*) from test2 where object_id not in(select object_id from test1);
执行计划
----------------------------------------------------------
Plan hash value: 744350129
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 294 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT ANTI NA| | 71226 | 626K| 294 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | TEST1 | 1000 | 4000 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST2 | 72226 | 352K| 288 (1)| 00:00:04 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1051 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
338 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 test2 t1 where not exists
(select 1 from test1 t2 where t1.object_id=t2.object_id);
执行计划
----------------------------------------------------------
Plan hash value: 2788255037
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 294 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT ANTI| | 71226 | 626K| 294 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | TEST1 | 1000 | 4000 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST2 | 72226 | 352K| 288 (1)| 00:00:04 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1051 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
338 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
- EXISTS与IN, NOT EXISTS与NOT IN比较
- not in与not exists的区别
- not in 与not exists区别
- not in与not exists的区别
- IN&EXISTS与NOT IN&NOT EXISTS 的优化
- IN&EXISTS 与 NOT IN&NOT EXISTS
- IN&EXISTS 与 NOT IN&NOT EXISTS 子查询 区别
- IN&EXISTS 与 NOT IN&NOT EXISTS 区别
- exists 与not exists in 和not in用法
- not in\in与not exists\exists—SQLCookbook
- IN, EXISTS, NOT IN, NOT EXISTS
- in、not in、exists、not exists
- exists,in 和 not in ,not exists
- IN/NOT IN---EXISTS/NOT EXISTS理解
- in、exists、not in、not exists
- oracle in,exists,not in,not exists
- in,exists,not in ,not exists效率
- splice 删除数组元素
- 网络技巧---能上网但是网页打不开,网页打开速度慢解决方法
- 地理坐标系与投影坐标系的区别
- Navicat for MySQL使用手记(下）--实现自动备份数据库
- poj2398 几何运算（叉乘---判断是否在凸四边形内的判断（二分、排序））
- 再探not in 与not exists
- vim pig语法高亮
- 南阳ACM29-求转置矩阵问题
- SQL常用函数集锦
- 如何分配动态数组
- SQL逻辑查询处理顺序特别提醒
- 寻找模块齐全的STK软件
- C++实现算术四则运算
- AE完整版中文软件免费下载安装及破解
