`
楚若之夜
  • 浏览: 124645 次
  • 性别: Icon_minigender_1
  • 来自: 大连
社区版块
存档分类
最新评论

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 这两种使用频率较高的语句,能使用最优的查询计划.

分享到：
| 用EXP、IMP迁移包含物化视图日志的数据
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle: minus | in | exists

    在性能方面，`EXISTS`通常比`IN`和`MINUS`更快，因为它在找到一个匹配项后就会停止执行，而`IN`和`MINUS`会完成整个集合的比较。然而，这取决于具体的数据分布和索引策略，因此在实际应用中，应根据具体情况选择最...

    MySQL中对于not in和minus使用的优化

    在MySQL数据库中，`NOT IN` 和 `MINUS` 是两种常用的子查询操作，用于从结果集中排除特定的行。然而，这些操作在某些情况下可能会导致性能问题，特别是在处理大量数据时。本篇文章将探讨如何对这两种操作进行优化，...

    oracl数据库查询优化[定义].pdf

    6. **MINUS子句和NOT IN**：MINUS子句通常比NOT IN更快，但需注意，NOT IN和NOT EXISTS在处理NULL值时有所不同，NOT EXISTS在某些情况下可能更快，因为它可以使用索引。 7. **LIKE操作符的效率**：`LIKE`操作符，...

    Oracle数据库应用教程--子查询与高级查询.pptx

    高级查询还包括了对多个表的连接操作，如简单连接、内连接、外连接和交叉连接： - **简单连接**（JOIN）：连接两个或更多表，没有特定连接条件。 - **内连接**（INNER JOIN）：只返回两个表中匹配的行。 - **外连接...

    SQL编程规范(还不错哦)

    1. 尽量少用嵌套查询，使用 not exist 代替 not in 子句。 2. 用多表连接代替 EXISTS 子句。 3. 少用 DISTINCT，用 EXISTS 代替。 4. 使用 UNION ALL、MINUS、INTERSECT 提高性能。 5. 使用 ROWID 提高检索速度。 6....

    oracle部分面试题-DBA数据库管理员JAVA程序员架构师必看 (3).docx

    SELECT id, name FROM emp1 e WHERE e.id NOT IN (SELECT id FROM sext); ``` 2. 使用MINUS操作符： ```sql SELECT id FROM emp1 MINUS SELECT id FROM sext; ``` 3. 使用&lt;&gt; ALL子查询： ```sql SELECT * ...

    sql优化心得

    - 方法a：`SELECT * FROM A WHERE A.C NOT IN (SELECT C FROM B)`可能效率较低。 - 方法b：`SELECT A.* FROM A, B WHERE A.C = B.C (+) AND B.C IS NULL`使用外连接，但注意性能问题。 - 方法c：`SELECT * FROM A...

    SQL面试题目.pdf

    此题使用自连接和集合操作（如`MINUS`或`EXCEPT`，在某些数据库系统中可能为`NOT EXISTS`）找出这些学生。 以上就是基于题目内容的SQL知识点解析，涵盖了子查询、JOIN操作、聚合函数、条件过滤、集合操作等核心概念...

    oracle部分面试题-DBA数据库管理员JAVA程序员架构师必看.docx

    1. 子查询和集合操作：`IN`, `NOT IN`, `MINUS`, `ALL`, `EXISTS`, `INTERSECT`。 2. `JOIN`操作：`LEFT JOIN`, `INNER JOIN`，以及如何使用连接条件。 3. 聚合函数：`COUNT`, `SUM`，以及`GROUP BY`语句。 4. 计算...

    oracle部分面试题-DBA数据库管理员JAVA程序员架构师必看.pdf

    左外连接会返回所有员工记录，如果性别为空，则`s.sex`为`NULL`。 5. 使用`NOT EXISTS`： ```sql SELECT * FROM emp1 e WHERE NOT EXISTS (SELECT * FROM sext s WHERE e.id = s.id); ``` 这里查找的是不存在...

    SQL编程书写的规范及一些书写优化性能建议

    1. **减少嵌套查询**：用`NOT EXISTS`替代`NOT IN`，减少表扫描。 2. **多表连接代替EXISTS**：在适当情况下，用多表连接替换`EXISTS`子句。 3. **避免DISTINCT**：使用`EXISTS`代替`DISTINCT`，提高效率。 4. **...

    sql面试题库

    此方法通过自连接实现，通过比较两支球队的名称来确保只输出一次比赛组合，避免了重复。 ### 4. 查询所有月份的发生额都比特定科目高的科目 题目要求查询所有月份的发生额都比指定科目（例如101科目）的发生额高的...

    Advanced SQL (1).ppt

    如果希望避免使用MINUS，可以使用NOT EXISTS子句或者LEFT JOIN来达到类似的效果。 接下来，我们将讨论高级SQL JOIN操作符语法。JOIN是连接两个或更多表的关键字，以基于它们之间的关联获取数据。例如，NATURAL JOIN...

    sql 书写规范

    1. 尽量少用嵌套查询，如必须，请用 not exist 代替 not in 子句。 2. 用多表连接代替 EXISTS 子句。 3. 少用 DISTINCT，用 EXISTS 代替。 4. 使用 UNION ALL、MINUS、INTERSECT 提高性能。 5. 使用 ROWID 提高检索...

    经典全面SQL 面试题

    注意，GROUP BY不能与WHERE中的聚合函数一起使用，通常可以使用EXISTS替换IN，但在包含聚合函数的IN子句中，不能用EXISTS替换。 3. 球队比赛组合： 给定一个包含球队名称的表，查询所有可能的比赛组合。这个问题...

    SQL查询效率注意事项小结

    6. **子查询优化**：合理使用`IN`、`NOT IN`、`EXISTS`和`NOT EXISTS`。当子查询结果无重复时，`IN`和`EXISTS`可以互换；当保证子查询结果非空时，`NOT IN`和`NOT EXISTS`可用`LEFT JOIN`替代。尽量避免不必要的子...

    Oracle和SQL_Server的语法区别

    - Oracle 支持 `INTERSECT` 和 `MINUS` 集合运算符，而 SQL Server 用 `EXISTS` 和 `NOT EXISTS` 子句来实现相同的效果。 对于示例中的 `INTERSECT` 和 `MINUS` 运算符，可以使用 `EXISTS` 或 `NOT EXISTS` 来达到...

    oracle优化方法

    NOT EXISTS也比NOT IN更高效，原因与EXISTS类似。 #### 18. EXISTS与DISTINCT 在某些情况下，使用EXISTS代替DISTINCT可以提高性能。 #### 19. SQL语句的编写技巧 编写高效的SQL语句需要注意多方面的细节，如避免...

    Oracle性能问题总结

    Oracle 的 From 子句表的顺序，Where 子句表达式的顺序，避免使用 ‘*’，使用 Truncate 代替 Delete，使用索引尽量减少对表的查询，避免使用右匹配或者中间匹配的模糊查询，并注意使用 exists、NOT EXISTS、NOT IN ...

Magicbox
Global site tag (gtag.js) - Google Analytics