对于in和existsnot in和not exists还是有很多的人有疑惑更有甚者禁用not in所有的地方都要用not exists它真的高效吗?通过下面的使用我们来证明
先制造一些数据
SQL> drop table test purge;
SQL> drop table test purge;
SQL> create table test as select * from dba_objects where rownum <=;
SQL> create table test as select * from dba_objects;
SQL> exec dbms_statsgather_table_stats(usertest)
SQL> exec dbms_statsgather_table_stats(usertest)
SQL> set autotrace traceonly
in和exists原理及性能实验
SQL> select * from test t where tobject_id in (select tobject_id from test t)
已选择行
执行计划
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
|* | HASH JOIN SEMI | | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | K| ()| :: |
Predicate Information (identified by operation id)
access(TOBJECT_ID=TOBJECT_ID)
统计信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL> select * from test t
where exists (select from test t where tobject_id = tobject_id)
已选择行
执行计划
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
|* | HASH JOIN SEMI | | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | K| ()| :: |
Predicate Information (identified by operation id)
access(TOBJECT_ID=TOBJECT_ID)
统计信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
结论在oracle g中in 和 exists其实是一样的原理就是两张表做HASH JOIN SEMI也可以通过事件看到两条sql语句最终转换成同一条sql
not in和not exists原理及性能实验
not exists 比 not in效率高的例子
SQL> select count(*) from test where object_id not in(select object_id from test)
执行计划
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
| | SORT AGGREGATE | | | | | |
|* | FILTER | | | | | |
| | TABLE ACCESS FULL| TEST | | | ()| :: |
|* | TABLE ACCESS FULL| TEST | | | ()| :: |
Predicate Information (identified by operation id)
filter( NOT EXISTS (SELECT /*+ */ FROM TEST TEST WHERE
LNNVL(OBJECT_ID<>:B)))
filter(LNNVL(OBJECT_ID<>:B))
统计信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL> select count(*) from test t where not exists
(select from test t where tobject_id=tobject_id)
执行计划
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
| | SORT AGGREGATE | | | | | |
|* | HASH JOIN ANTI | | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | K| ()| :: |
Predicate Information (identified by operation id)
access(TOBJECT_ID=TOBJECT_ID)
统计信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
not in比not exists 效率高的例子
SQL> Set autotrace off
SQL> drop table test purge;
表已删除
SQL> drop table test purge;
表已删除
SQL> create table test as select * from dba_objects where rownum <=;
表已创建
SQL> create table test as select * from dba_objects;
表已创建
SQL> Insert into test select * from dba_objects;
已创建行
SQL> Insert into test select * from test;
已创建行
SQL> Insert into test select * from test;
已创建行
SQL> Commit;
提交完成
SQL> exec dbms_statsgather_table_stats(usertest)
PL/SQL 过程已成功完成
SQL> exec dbms_statsgather_table_stats(usertest)
PL/SQL 过程已成功完成
SQL> Set autotrace traceonly
SQL> select count(*) from test where object_id not in(select object_id from test)
执行计划
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
| | SORT AGGREGATE | | | | | |
|* | FILTER | | | | | |
| | TABLE ACCESS FULL| TEST | | | ()| :: |
|* | TABLE ACCESS FULL| TEST | | | ()| :: |
Predicate Information (identified by operation id)
filter( NOT EXISTS (SELECT /*+ */ FROM TEST TEST WHERE
LNNVL(OBJECT_ID<>:B)))
filter(LNNVL(OBJECT_ID<>:B))
统计信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL> select count(*) from test t where not exists
(select from test t where tobject_id=tobject_id)
执行计划
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
| | SORT AGGREGATE | | | | | |
|* | HASH JOIN ANTI | | | | ()| :: |
| | TABLE ACCESS FULL| TEST | | | ()| :: |
| | TABLE ACCESS FULL| TEST | K| K| ()| :: |
Predicate Information (identified by operation id)
access(TOBJECT_ID=TOBJECT_ID)
统计信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
结论not in 和not exists原理是nestedloops 与HASH JOIN的区别not in中的filter算法类似于nestedloops如果比较两者的性能就是比较nestedloops 与HASH JOIN的性能差异在本例子中
not in 性能 大于not exists test的数据量条test数量多万条
not exists 性能 大于not in test的数据量条test数量条