一个老话题: MINUS,外连接,NOT IN,NOT EXISTS 的效率比较

关于 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