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