关于 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 这两种使用频率较高的语句,能使用最优的查询计划.
相关推荐
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 ...
htmlStr =htmlStr.replaceAll("∉", "∉"); htmlStr =htmlStr.replaceAll("∋", "∋"); htmlStr =htmlStr.replaceAll("∏", "∏"); htmlStr =htmlStr.replaceAll("∑", "∑"); htmlStr =...
...本字符实体参考手册包括了数学符号、希腊字符、各种箭头记号、科技符号以及形状。...notin ∋ ∋ ∋ ni ∏ ∏ ∏ prod ∑ ∑ ∑ sum − − − minus ∗ ∗ ∗ lowast √ √ √ square root
IN BETWEEN LIKE ORDER BY 函数 COUNT GROUP BY HAVING ALIAS 表格链接 外部链接 CONCATENATE SUBSTRING TRIM 表格处理 CREATE TABLE CONSTRAINT NOT NULL UNIQUE CHECK 主键 外来键 CREATE VIEW CREATE INDEX ALTER...
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 复杂的分组 ...
§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 §...
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 复杂的分组 ...
可以使用AND或OR、NOT等连接相关的条件 计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序，使用圆括号()没有坏处，它能消除二义性。 and：条件与 如 SELECT prod_id,prod_price,prod_name FROM...
§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 §...
listtxt.zip This shows how to retrieve the text from the current selection in a listbox.<END><br>11 , list_index.zip This example tells you if the inputted List Index is selected or not.<END>...
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 ...