IN/EXISTS/NOT IN/NOT EXISTS 3-3
--Oracle 9.2.0
test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='9.2.0'
2 /
会话已更改。
已用时间: 00: 00: 00.00
test@GZSERVER> select count(*) from outer_large_t where id not in (select id from inner_large_t)
2 /
COUNT(*)
----------
29999
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=152 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=152 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=11 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=19 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
299 consistent gets
119 physical reads
…
test@GZSERVER> select count(*) from outer_large_t t1
2 where not exists (select null from inner_large_t t2 where t1.id = t2.id)
3 /
COUNT(*)
----------
29999
已用时间: 00: 00: 01.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=11 Card=2500 Bytes=32500)
4 2 INDEX (RANGE SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX) (C
ost=2 Card=700 Bytes=9100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
100106 consistent gets
0 physical reads
…
test@GZSERVER> select count(*) from outer_small_t where id not in (select id from inner_large_t)
2 /
COUNT(*)
----------
500
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=31 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1001 Bytes=13013)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=19 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
199 consistent gets
0 physical reads
…
test@GZSERVER> select count(*) from outer_small_t t1
2 where not exists (select null from inner_large_t t2 where t1.id = t2.id)
3 /
COUNT(*)
----------
500
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=50 Bytes=650)
4 2 INDEX (RANGE SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX) (C
ost=2 Card=700 Bytes=9100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2008 consistent gets
0 physical reads
…
-- 在本例中,
-- 从执行计划来看,不管外部表是大表还是小表,not in均使用了效率更高的hash反连接
-- 而not exists还是使用FILTER操作.
-- 从统计信息来看,在外部表为大表的时候,not in要远远优于not exists
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21670/，如需转载，请注明出处，否则将追究法律责任。
