MINUS,外连接,NOT IN,NOT EXISTS 的效率比较

最新推荐文章于 2021-04-10 20:23:40 发布
最新推荐文章于 2021-04-10 20:23:40 发布
阅读量4.9k 收藏
点赞数
分类专栏： sql优化

MINUS,外连接,NOT IN,NOT EXISTS 的效率比较

分类： Oracle 2011-06-24 11:03  415人阅读  评论(0)  收藏  举报

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

 

浅笑雨忆
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL语句教程.pdf
09-03
SQL 指令 SELECT DISTINCT WHERE AND OR 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 TABLE DROP TABLE TRUNCATE TABLE INSERT INTO UPDATE DELETE FROM 进阶 SQL UNION UNION ALL INTERSECT MINUS 子查询 EXISTS CASE 算排名 算中位数 算总合百分比 算累积总合百分比 SQL 语法 无论您是一位 SQL 的新手，或是一位只是需要对 SQL 复习一下的资料仓储业界老将，您 就来对地方了。这个 SQL 教材网站列出常用的 SQL 指令,包含以下几个部分： ♦ SQL 指令: SQL 如何被用来储存、读取、以及处理数据库之中的资料。 ♦ 表格处理: SQL 如何被用来处理数据库中的表格。 ♦ 进阶 SQL: 介绍 SQL 进阶概念，以及如何用 SQL 来执行一些较复杂的运算。 ♦ SQL 语法: 这一页列出所有在这个教材中被提到的 SQL 语法。 对于每一个指令， 我们将会先列出及解释这个指令的语法， 然后用一个例子来让读者了解这 个指令是如何被运用的。当您读完了这个网站的所有教材后，您将对 SQL 的语法会有一个 大致上的了解。另外，您将能够正确地运用 SQL 来由数据库中获取信息。笔者本身的经验 是，虽然要对 SQL 有很透彻的了解并不是一朝一夕可以完成的，可是要对 SQL 有个基本 的了解并不难。希望在看完这个网站后，您也会有同样的想法。
SQL语句教程.doc
05-31
SQL指令 2 SELECT 2 DISTINCT 3 WHERE 4 AND OR 5 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 UNIQUE 22 CHECK 23 主键 23 外来键 24 CREATE VIEW 26 CREATE INDEX 28 ALTER TABLE 29 DROP TABLE 31 TRUNCATE TABLE 31 INSERT INTO 32 UPDATE 33 DELETE FROM 34 进阶SQL 35 UNION 36 UNION ALL 37 INTERSECT 38 MINUS 39 子查询 40 EXISTS 42 CASE 43 算排名 44 算中位数 45 算总合百分比 47 算累积总合百分比 48 SQL语法 49
HTML 符号实体参考手册
01-21
HTML 符号实体参考手册 HTML 符号实体 本字符实体参考手册包括了数学符号、希腊字符、各种箭头记号、科技符号以及形状。 注释： 实体名称对大小写敏感。   HTML 支持的数学符号 字符 实体编号 实体名称 描述 ∀ ∀ ∀ for all ∂ ∂ ∂ part ∃ ∃ ∃ exists ∅ ∅ ∅ empty ∇ ∇ ∇ nabla ∈ ∈ ∈ isin ∉ ∉ ∉ notin ∋ ∋ ∋ ni ∏ ∏ ∏ prod ∑ ∑ ∑ sum &minus; &minus; &minus; minus ∗ ∗ ∗ lowast √ √ √ square root
java替换html特殊字符工具类
06-19
替换html中特殊字符 htmlStr =htmlStr.replaceAll("∀", "∀"); htmlStr =htmlStr.replaceAll("∂", "∂"); htmlStr =htmlStr.replaceAll("&exists;", "∃"); htmlStr =htmlStr.replaceAll("∅", "∅"); htmlStr =htmlStr.replaceAll("∇", "∇"); htmlStr =htmlStr.replaceAll("∈", "∈"); htmlStr =htmlStr.replaceAll("∉", "∉"); htmlStr =htmlStr.replaceAll("∋", "∋"); htmlStr =htmlStr.replaceAll("∏", "∏"); htmlStr =htmlStr.replaceAll("∑", "∑"); htmlStr =htmlStr.replaceAll("&minus;", "&minus;"); htmlStr =htmlStr.replaceAll("∗", "∗"); htmlStr =htmlStr.replaceAll("√", "√"); htmlStr =htmlStr.replaceAll("∝", "∝"); htmlStr =htmlStr.replaceAll("∞", "∞"); htmlStr =htmlStr.replaceAll("∠", "∠"); ...... 这些字符全替换，虽然分高点但是工作量很大，从html 帮助文档中挨个提出来
oracle minus 利用率,oracle minus用法
weixin_28835583的博客
04-05 535
Oracle Minus关键字SQL中的MINUS关键字SQL中有一个MINUS关键字，它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果 中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这 笔资料就被抛弃，其语法如下：[SQL Segment 1...
oracle取差集效率如何,Oracle Minus 取差集
weixin_29761519的博客
04-10 356
Oracle MinuskeywordSQL中的MINUSkeywordSQL中有一个MINUSkeyword，它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。假设有的话，那这一笔记录就被去除，而不会在最后的结果中出现。假设第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃，其语法例如以下：[S...
Oracle-性能优化（二）
bk_huangzongbo的博客
12-05 352
Oracle-性能优化（二）
oracle minus 利用率,oracle 两表数据对比---minus
weixin_31089065的博客
04-05 284
1 引言在程序设计的过程中，往往会遇到两个记录集的比较。如华东电网PMS接口中实现传递一天中变更(新增、修改、删除)的数据。实现的方式有多种，如编程存储过程返回游标，在存储过程中对两批数据进行比较等等。本文主要讨论利用ORACLE的MINUS函数，直接实现两个记录集的比较。2 实现步骤假设两个记录集分别以表的方式存在，原始表为A，产生的比较表为B。2.1 判断原始表和比较表的增量差异利用MINUS...
Oracle_Database_11g完全参考手册.part3/3
08-18
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 复杂的分组 ...
一个老话题: MINUS,外连接,NOT IN,NOT EXISTS 的效率比较
旺仔专栏
07-25 4207
<br />关于 MINUS,外连接,NOT IN,NOT EXISTS 的效率比较,绝对是一个很老很老的话题了.<br />如果要完成这个需求:"取出一些记录,在表A中,不在表B中", 你会采用哪种方案?为什么会采用这种方案?<br />我作了一个实验, 发现随着数据库版本的不同,CBO的工作方式也有变化.<br />本文仅限于一般性的分析, 不涉及internal.<br /> <br />一.  概述<br />首先, 我必须纠正自己的一个"错误认识": MINUS的效率很低.<br />针对上面提到
NOT EXIST和NOT IN 和MINUS的用法
07-15 1253
①NOT EXISTS一般都会比较慢 UPDATE  A SET DATE=sysdate WHERE LINK_TAISHOGAI_FG = '0'     AND NOT EXISTS (SELECT 1  FROM   FROM   B  WHERE  A.ID1= B.ID1  AND    A.ID2 = B.ID2  AND   A.ID3 = B.ID3  A
minus vs not exists
shandeai520的专栏
11-04 485
minus会对所有表进行全表扫描，除非where语句里的字段加了索引。 minus需要两个查询拥有相同数量的列，每一列都得类型相同。 minus会返回第一个查询的所有记录，列不一定和第二个查询的结果的列对应。 minus默认对两个查询都进行排序。 not exists每行都读一遍子查询，如果子查询的表的太大，不宜使用。如果关联字段加了索引，那么就只需要执行索引扫描。 ...
Oracle之minus求差集（与该功能相同的有not in、not exists）
侯风飞的博客
05-30 1664
minus语法：A minus B只去除A跟B的交集部分，将A剩余自己独有的部分输出显示。（1）minus语法（2）minus解释 与minus功能相同的有（not in、not exist）关键字 例子（1）原表（2）例子1：部分相同（3）例子2：完全相同 ...
oracle中关于in和exists，not in 和 not exists、关联子查询、非关联子查询
bianxq
08-20 954
                oracle中关于in和exists，not in 和 not exists in和exists     in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。 not exists:做NL，对子查询先查，有个虚表，有确定值，所以就算子查询有NULL最终也有值返回 not in:做hash，对子查询...
oracle minus 利用率,Oracle Minus 取差集(也可以用来做分页，但效率不高)
weixin_30847063的博客
04-05 255
Oracle Minus关键字SQL中的MINUS关键字SQL中有一个MINUS关键字，它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃，其语法如下：[SQL Segment 1]M...
oracle minus 利用率,弱弱的问下minus和not exists究竟哪个效率好点！
weixin_39900830的博客
04-05 331
SQL> explain plan for2select * from objects3minus4select * from objects15/SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------...
[Oracle] minus 和 not exists比较
cizhiyou2391的博客
02-22 563
SQL> select count(*) from test; COUNT(*) ---------- 28835328 Elapsed: 00:00:09.48 SQL> select c...
我在ORACLE使用minus函数时报错SQL command not properly ended
最新发布
06-07
同时，MINUS函数只能用于两个SELECT语句之间进行集合的比较，两个SELECT语句的列数和数据类型必须相同。 以下是一个正确使用MINUS函数的示例： ``` SELECT column1, column2 FROM table1 MINUS SELECT column1, ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值