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