比较了一下not exists & not in，发现没什么区别。看如下实验。
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> set time on
11:42:41 test@GZSERVER> set timing on
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> drop table outer_large_t
11:42:41 2 /
表已丢弃。
已用时间: 00: 00: 00.01
11:42:41 test@GZSERVER> create table outer_large_t
11:42:41 2 (id number not null,
11:42:41 3 c1 varchar2(100),
11:42:41 4 c2 varchar2(100)
11:42:41 5 )
11:42:41 6 /
表已创建。
已用时间: 00: 00: 00.00
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> alter table outer_large_t add primary key (id)
11:42:41 2 /
表已更改。
已用时间: 00: 00: 00.00
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> drop table outer_small_t
11:42:41 2 /
表已丢弃。
已用时间: 00: 00: 00.00
11:42:41 test@GZSERVER> create table outer_small_t
11:42:41 2 as select *from outer_large_t
11:42:41 3 where 1=2
11:42:41 4 /
表已创建。
已用时间: 00: 00: 00.01
11:42:41 test@GZSERVER> create index idx_outer_small_t_id on outer_small_t(id)
11:42:41 2 /
索引已创建。
已用时间: 00: 00: 00.00
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> drop table inner_large_t
11:42:41 2 /
表已丢弃。
已用时间: 00: 00: 00.01
11:42:42 test@GZSERVER> create table inner_large_t
11:42:42 2 (id number not null,
11:42:42 3 c3 varchar2(100),
11:42:42 4 c4 varchar2(100)
11:42:42 5 )
11:42:42 6 /
表已创建。
已用时间: 00: 00: 00.00
11:42:42 test@GZSERVER> create index idx_inner_large_t_1 on inner_large_t(id,c3)
11:42:42 2 /
索引已创建。
已用时间: 00: 00: 00.00
11:42:42 test@GZSERVER>
11:42:42 test@GZSERVER> drop table inner_small_t
11:42:42 2 /
表已丢弃。
已用时间: 00: 00: 00.00
11:42:42 test@GZSERVER> create table inner_small_t
11:42:42 2 as select *from inner_large_t
11:42:42 3 where 1=2
11:42:42 4 /
表已创建。
已用时间: 00: 00: 00.01
11:42:42 test@GZSERVER> create index idx_inner_small_t_1 on inner_small_t(id,c3)
11:42:42 2 /
索引已创建。
已用时间: 00: 00: 00.00
11:42:42 test@GZSERVER>
11:42:42 test@GZSERVER> declare
11:42:42 2 begin
11:42:42 3 for i in 1..50000 loop
11:42:42 4 insert into outer_large_t values (i,'test','test');
11:42:42 5 end loop;
11:42:42 6 for i in 1..1000 loop
11:42:42 7 insert into outer_small_t values (i,'test','test');
11:42:42 8 end loop;
11:42:42 9 for i in 30000..50000 loop
11:42:42 10 insert into inner_large_t values (i,'test','test');
11:42:42 11 end loop;
11:42:42 12 for i in 3000..5000 loop
11:42:42 13 insert into inner_small_t values (i,'test','test');
11:42:42 14 end loop;
11:42:42 15 commit;
11:42:42 16 end;
11:42:42 17 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 41.06
11:43:24 test@GZSERVER>
11:43:24 test@GZSERVER> analyze table outer_large_t compute statistics for table for all indexes
11:43:24 2 /
表已分析。
已用时间: 00: 00: 02.04
11:43:26 test@GZSERVER> analyze table outer_small_t compute statistics for table for all indexes
11:43:26 2 /
表已分析。
已用时间: 00: 00: 00.01
11:43:26 test@GZSERVER> analyze table inner_large_t compute statistics for table for all indexes
11:43:26 2 /
表已分析。
已用时间: 00: 00: 00.07
11:43:27 test@GZSERVER> analyze table inner_large_t compute statistics for table for all indexes
11:43:27 2 /
表已分析。
已用时间: 00: 00: 00.07
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> set autotrace on
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_large_t
11:43:28 2 where id not in
11:43:28 3 (select id from inner_large_t)
11:43:28 4 /
COUNT(*)
----------
29999
已用时间: 00: 00: 00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=161 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (RIGHT ANTI) (Cost=161 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=13 Card=20001 Bytes=260013)
4 2 INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE
)) (Cost=22 Card=50000 Bytes=650000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
160 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11:43:28 test@GZSERVER> select count(*) from outer_large_t outer_t
11:43:28 2 where not exists
11:43:28 3 (select id from inner_large_t where id = outer_t.id)
11:43:28 4 /
COUNT(*)
----------
29999
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=161 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (RIGHT ANTI) (Cost=161 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=13 Card=20001 Bytes=260013)
4 2 INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE
)) (Cost=22 Card=50000 Bytes=650000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
160 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_small_t
11:43:28 2 where id not in
11:43:28 3 (select id from inner_large_t)
11:43:28 4 /
COUNT(*)
----------
1000
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=16 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1000 Bytes=13000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=13 Card=20001 Bytes=260013)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11:43:28 test@GZSERVER> select count(*) from outer_small_t outer_t
11:43:28 2 where not exists
11:43:28 3 (select id from inner_large_t where id = outer_t.id)
11:43:28 4 /
COUNT(*)
----------
1000
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=16 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1000 Bytes=13000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=13 Card=20001 Bytes=260013)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_large_t
11:43:28 2 where id not in
11:43:28 3 (select id from inner_small_t)
11:43:28 4 /
COUNT(*)
----------
47999
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (RIGHT ANTI) (Cost=26 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_SMALL_T_1' (INDEX
) (Cost=3 Card=2001 Bytes=26013)
4 2 INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE
)) (Cost=22 Card=50000 Bytes=650000)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
133 consistent gets
0 physical reads
912 redo size
382 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
11:43:28 test@GZSERVER> select count(*) from outer_large_t outer_t
11:43:28 2 where not exists
11:43:28 3 (select id from inner_small_t where id = outer_t.id)
11:43:28 4 /
COUNT(*)
----------
47999
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (RIGHT ANTI) (Cost=26 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_SMALL_T_1' (INDEX
) (Cost=3 Card=2001 Bytes=26013)
4 2 INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE
)) (Cost=22 Card=50000 Bytes=650000)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_small_t
11:43:28 2 where id not in
11:43:28 3 (select id from inner_small_t)
11:43:28 4 /
COUNT(*)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6906/viewspace-21611/ ，如需转载，请注明出处，否则将追究法律责任。
