关于 MINUS,外连接,NOT IN,NOT EXISTS 的效率比较,绝对是一个很老很老的话题了.
如果要完成这个需求:"取出一些记录,在表A中,不在表B中", 你会采用哪种方案?为什么会采用这种方案?
我作了一个实验, 发现随着数据库版本的不同,CBO的工作方式也有变化.
本文仅限于一般性的分析, 不涉及internal.
一. 概述
首先, 我必须纠正自己的一个"错误认识": MINUS的效率很低.
针对上面提到的需求,采用哪种查询方式,其效率取决于:
1. 两个表的数据量,以及数据分布;
2. 表有没有经过分析;
3. 子查询中是否包含NULL值 (很重要);
4. 是否存在索引;
5. 数据库版本:不同版本的数据库,优化器的工作方式会有差异.
二. 环境
首先测试的数据库的版本是Oracle 9.0.1.5,接下来我会在10G中也测试一下.
两个与优化器工作原理相关的的参数都用的是缺省值.
|
optimizer_index_caching |
integer |
0 |
|
optimizer_index_cost_adj |
integer |
100 |
表T1,T2,结构相同,但是数据不同.T2可以看成是T1的子集.
表的结构很简单,都取自dba_objects视图
create table t1 as select * from dba_objects where rownum<=13000;
create table t2 as select * from dba_objects where rownum<=11000;
Create index ix_t2 on t2(object_id);
三. 测试
目标: 我想把T1表中其它的数据也导入到T2表.
方式: 启动SQL TRACE, 再用tkprof对生成的trace文件进行解析.
首先用 NOT IN来执行,
1. 使用 NOT IN
select count(*) from t1 where object_id not in ( select object_id from t2);
|
call |
count |
cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.00 |
0.01 |
0 |
0 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
16.84 |
18.05 |
190 |
1153542 |
0 |
1 |
|
total |
4 |
16.84 |
18.06 |
190 |
1153542 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
FILTER |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
TABLE ACCESS FULL T2 |
结论: 两次全表扫描的代价显然太高了,无疑是效率最低的方案.
2. 使用MINUS
alter system flush shared_pool;
alter session set sql_trace=true;
Select count(*) from
(select object_id from t1
minus
select object_id from t2
);
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.01 |
0.00 |
0 |
2 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.04 |
0.03 |
0 |
356 |
0 |
1 |
|
total |
4 |
0.05 |
0.03 |
0 |
358 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
VIEW |
|
2000 |
MINUS |
|
13000 |
SORT UNIQUE |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
SORT UNIQUE |
|
11000 |
TABLE ACCESS FULL T2 |
结论: 看上去效率很不错
3. 使用 not exists
alter system flush shared_pool;
alter session set sql_trace=true;
select count(*)
from t1
where not exists
(select null from t2 where t2.object_id = t1.object_id);
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.01 |
0.00 |
0 |
2 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.08 |
0.21 |
24 |
26197 |
0 |
1 |
|
total |
4 |
0.09 |
0.21 |
24 |
26199 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
FILTER |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
INDEX RANGE SCAN (object id 108538) |
结论: 效率比NOT IN 好很多,但是不如MINUS,并且存在物理读.
4. 最后来看看我比较喜欢用的外连接(+)
alter system flush shared_pool;
alter session set sql_trace=true;
select count(*)
from t1, t2
where t1.object_id = t2.object_id(+)
and t2.object_id IS NULL;
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.01 |
0.00 |
0 |
2 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.05 |
0.05 |
0 |
13222 |
0 |
1 |
|
total |
4 |
0.06 |
0.05 |
0 |
13224 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
FILTER |
|
13000 |
NESTED LOOPS OUTER |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
INDEX RANGE SCAN (object id 108538) |
结论: 比NOT EXISTS的效果好,不如MINUS.从查询计划来看,显然不是一个最优计划.
四. 对表分析后再测试
analyze table t1 compute statistics;
1. NOT IN
alter system flush shared_pool;
alter session set sql_trace=true;
select count(*)
from t1
where object_id not in (select object_id from t2);
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.02 |
0.00 |
0 |
0 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
16.04 |
0.05 |
0 |
0 |
0 |
1 |
|
total |
4 |
16.06 |
0.05 |
0 |
0 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
FILTER |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
TABLE ACCESS FULL T2 |
结论:与分析前相比,没有任何改变
2. MINUS
alter system flush shared_pool;
alter session set sql_trace=true;
Select count(*) from
(select object_id from t1
minus
select object_id from t2
);
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.00 |
0.00 |
0 |
76 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.05 |
0.04 |
0 |
356 |
0 |
1 |
|
total |
4 |
0.05 |
0.04 |
0 |
342 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
VIEW |
|
2000 |
MINUS |
|
13000 |
SORT UNIQUE |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
SORT UNIQUE |
|
11000 |
TABLE ACCESS FULL T2 |
结论: 查询计划没有改变, 虽然各项指标有些不同.
3. 使用NOT EXISTS
alter system flush shared_pool;
alter session set sql_trace=true;
select count(*)
from t1
where not exists
(select null from t2 where t2.object_id = t1.object_id);
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.01 |
0.02 |
0 |
144 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.08 |
0.08 |
0 |
26197 |
0 |
1 |
|
total |
4 |
0.09 |
0.10 |
0 |
26341 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
FILTER |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
INDEX RANGE SCAN (object id 108538) |
结论: 查询计划也没有改变.
4. 使用 外连接
alter system flush shared_pool;
alter session set sql_trace=true;
select count(*)
from t1, t2
where t1.object_id = t2.object_id(+)
and t2.object_id IS NULL;
alter session set sql_trace=false;
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.01 |
0.00 |
0 |
1 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.02 |
0.01 |
0 |
223 |
0 |
1 |
|
total |
4 |
0.03 |
0.01 |
0 |
224 |
0 |
1 |
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE |
|
2000 |
FILTER |
|
13000 |
HASH JOIN OUTER |
|
13000 |
TABLE ACCESS FULL T1 |
|
11000 |
INDEX FAST FULL SCAN (object id 108538) |
结论: 经过分析以后,使用了HASH JOIN,效率提高很明显.这是一个正确的查询计划.
总结:这四种查询模式中使用外连接的效率最高.
五. 在10G中测试
T1: 10000
T2: 9800
NOT IN
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
7.65 |
7.47 |
135 |
685810 |
0 |
1 |
|
total |
4 |
7.65 |
7.47 |
135 |
685810 |
0 |
1 |
执行计划:
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE (cr=685810 pr=135 pw=0 time=7479614 us) |
|
200 |
FILTER (cr=685810 pr=135 pw=0 time=7474258 us) |
|
10000 |
TABLE ACCESS FULL T1 (cr=138 pr=135 pw=0 time=40407 us) |
|
9800 |
TABLE ACCESS FULL T2 (cr=685672 pr=0 pw=0 time=7366891 us) |
对T1进行分析后
|
call |
count |
Cpu |
elapsed |
disk |
query |
current |
rows |
|
Parse |
1 |
0.00 |
0.01 |
0 |
3 |
0 |
0 |
|
Execute |
1 |
0.00 |
0.00 |
0 |
0 |
0 |
0 |
|
Fetch |
2 |
0.01 |
0.01 |
22 |
165 |
0 |
1 |
|
total |
4 |
0.01 |
0.02 |
22 |
168 |
0 |
1 |
执行计划：
|
Rows |
Row Source Operation |
|
1 |
SORT AGGREGATE (cr=165 pr=22 pw=0 time=15933 us) |
|
200 |
HASH JOIN ANTI (cr=165 pr=22 pw=0 time=15973 us) |
|
10000 |
TABLE ACCESS FULL T1 (cr=138 pr=0 pw=0 time=10075 us) |
|
9800 |
INDEX FAST FULL SCAN IX_T2 (cr=27 pr=22 pw=0 time=10529 us)(object id 52081) |
另外, 通过对NOT EXISTS的分析,发现它的效率要好于MINUS,这也是一个变化.
六. 结束语
从上面的对比分析,可以得出这样的结论:
10G的CBO要比9i的CBO智能了不少, 对于NOT IN 和NOT EXISTS 这两种使用频率较高的语句,能使用最优的查询计划.
博文来源：http://lovewinter.itpub.net/post/493/5599