3) 各个版本下CBO对NOT IN、NOT EXISTS的不同
假定各实验表在id列上均有not null约束。
test@GZSERVER>
test@GZSERVER> prompt -- 各个版本cbo的影响
-- Oracle 10g
test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='10.1.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.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=278 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=23 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
385 recursive calls
0 db block gets
371 consistent gets
220 physical reads
…
> 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: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=278 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=23 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
299 consistent gets
119 physical reads
…
> 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=ALL_ROWS (Cost=45 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=45 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=42 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
202 recursive calls
0 db block gets
233 consistent gets
0 physical reads
…
> 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=ALL_ROWS (Cost=45 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=45 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=42 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
199 consistent gets
0 physical reads
…
-- 10g中不管是not in还是not exists均使用了hash反连接
-- 执行计划一样,但从统计信息来看not in的recursive calls更高并且有排序操作.
-- not exists没有排序操作是因为重用了上次not in排序产生的排序
-- (不妨先运行not exists然后运行not in,会发现not in没有排序的统计)
-- 同样的会发现recursive calls也降低了，见如下测试
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: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=278 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=23 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
299 consistent gets
119 physical reads
…
test@GZSERVER> select count(*) from outer_large_t where id not in (select id fro
m inner_large_t)
2 /
COUNT(*)
----------
29999
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=278 Card=1 Bytes=26)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=23 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
Statistics
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21669/，如需转载，请注明出处，否则将追究法律责任。
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员