关于 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_objectswhere rownum<=13000;
create table t2 as select * from dba_objectswhere rownum<=11000;
Create index ix_t2 on t2(object_id);
三. 测试
目标: 我想把T1表中其它的数据也导入到T2表.
方式: 启动SQL TRACE, 再用tkprof对生成的trace文件进行解析.
首先用 NOT IN来执行,
1. 使用 NOT IN
select count(*)from t1where 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
分享到：
相关推荐
对于每一个指令， 我们将会先列出及解释这个指令的语法， 然后用一个例子来让读者了解这 个指令是如何被运用的。当您读完了这个网站的所有教材后，您将对 SQL 的语法会有一个 大致上的了解。另外，您将能够正确地...
select：从一个或多个表中检索一个或多个数据列。包含信息：想选择什么表，从什么地方选择。必须要有From子句。(最常用) 当从多张表里查询的时候，会产生笛卡尔积；可用条件过滤它。 当两个表有相同字段时必须加...
IN 5 BETWEEN 6 LIKE 7 ORDER BY 8 函数 10 COUNT 11 GROUP BY 12 HAVING 13 ALIAS 13 表格链接 15 外部链接 16 CONCATENATE 17 SUBSTRING 18 TRIM 19 表格处理 21 CREATE TABLE 21 CONSTRAINT 22 NOT NULL 22 ...
13.2.3 用外部连接代替NOTIN 13.2.4 用NOTEXISTS代替NOTIN 13.3 自然连接和内部连接 13.4 UNION、INTERSECT和MINUS 13.4.1 IN子查询 13.4.2 UNION、INTERSECT和MiNUS的限制 第14章 一些复杂的技术 14.1 复杂的分组 ...
13.2.3 用外部连接代替NOTIN 13.2.4 用NOTEXISTS代替NOTIN 13.3 自然连接和内部连接 13.4 UNION、INTERSECT和MINUS 13.4.1 IN子查询 13.4.2 UNION、INTERSECT和MiNUS的限制 第14章 一些复杂的技术 14.1 复杂的分组 ...
...本字符实体参考手册包括了数学符号、希腊字符、各种箭头记号、科技符号以及形状。...notin ∋ ∋ ∋ ni ∏ ∏ ∏ prod ∑ ∑ ∑ sum − − − minus ∗ ∗ ∗ lowast √ √ √ square root
htmlStr =htmlStr.replaceAll("∉", "∉"); htmlStr =htmlStr.replaceAll("∋", "∋"); htmlStr =htmlStr.replaceAll("∏", "∏"); htmlStr =htmlStr.replaceAll("∑", "∑"); htmlStr =...
§10.1.8 Not in和Not Exists 218 §10.1.9 关于 COPY命令 218 §10.1.10 列值为NULL情形的处理 219 §10.1.11 使用 product_user_file来限制用户使用产品 220 §10.2 常用技巧 221 §10.2.1 long 类型的查询 222 §...
§10.1.8 Not in和Not Exists 218 §10.1.9 关于 COPY命令 218 §10.1.10 列值为NULL情形的处理 219 §10.1.11 使用 product_user_file来限制用户使用产品 220 §10.2 常用技巧 221 §10.2.1 long 类型的查询 222 §...
1 , TaskBar_v0.5.zip "Form_Taskbar is a control for Visual Basic which, once placed onto a form, makes the form act like the Taskbar (minus the Start Menu)." -- David Newcum<END><br>2 , ...
not added on as scripts to be interpreted slowly but as fully compiled and integrated functions of the system. Once installed, the customized features will be retained with each upgrade of the system ...