精华内容
下载资源
问答
  • explain plan for2select * from objects3minus4select * from objects15/SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------...

    SQL> explain plan for

    2  select * from objects

    3  minus

    4  select * from objects1

    5  /

    SQL> select * from table(dbms_xplan.display());

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------

    Plan hash value: 1313218784

    --------------------------------------------------------------------------------

    | Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Ti

    --------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT    |          | 67090 |  5178K|       |  1334   (1)| 00

    |   1 |  MINUS              |          |       |       |       |            |

    |   2 |   SORT UNIQUE       |          | 67090 |  5175K|    11M|  1330   (1)| 00

    |   3 |    TABLE ACCESS FULL| OBJECTS  | 67090 |  5175K|       |    95   (2)| 00

    |   4 |   SORT UNIQUE       |          |    30 |  2370 |       |     4  (25)| 00

    |   5 |    TABLE ACCESS FULL| OBJECTS1 |    30 |  2370 |       |     3   (0)| 00

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------

    SQL> explain plan for

    2  select *

    3    from objects

    4   where not exists

    5   (select * from objects1 where objects1.object_id = objects.object_id)

    6  /

    Explained.

    SQL> select * from table(dbms_xplan.display());

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------

    Plan hash value: 212929155

    --------------------------------------------------------------------------------

    | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time

    --------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT     |             | 67060 |  6024K|    96   (2)| 00:00:

    |*  1 |  HASH JOIN RIGHT ANTI|             | 67060 |  6024K|    96   (2)| 00:00:

    |   2 |   INDEX FULL SCAN    | PK_OBJECTS1 |    30 |   390 |     1   (0)| 00:00:

    |   3 |   TABLE ACCESS FULL  | OBJECTS     | 67090 |  5175K|    95   (2)| 00:00:

    --------------------------------------------------------------------------------

    展开全文
  • 当前表A、B的数据量很小以上三个sql查询效率上是不同的,当量大的情况下(1)和(2)(3)效率差距较大, 本人只使用时的数据量(A表4800条记录,B表11600条记录),执行时间如下 (1) 139S (2) 3S (3) 2.4S 均是初始...
    例子:
    
    create table A (
    num number(10));
    create table B (
    num number(10));

    表A中记录为1 2 3 9
    表B中记录为2 3 4 5

    现在要查询出1 9,可以用下面三个语句
    (1)select a.num from A a where a.num not in (select b.num from B b);
    (2)select a.num from A a minus select b.num from B b (这里a.num和b.num前不需要加distinct也会自动列出不重复的数据,要求两个查询出的列数相同,字段类型相同)
    (3)select a.num from A a where not exists (select b.num from B b where b.num=a.num)

    当前表A、B的数据量很小以上三个sql查询效率上是不同的,当量大的情况下(1)和(2)(3)效率差距较大,
    本人只使用时的数据量(A表4800条记录,B表11600条记录),执行时间如下
    (1) 139S
    (2) 3S
    (3) 2.4S
    均是初始执行时间

    后期再研究为什么效率会如此大的差距

    补充一(转自[url=http://www.itpub.net/forum.php?mod=viewthread&tid=1476690]ITPUB论坛[/url]):
    minus 剔重且会排序
    not exists/not in所谓的anti join,当然前者是真正的anti join,not in可能遇到null麻烦点,可能性能低,如果解决了null的问题,not exists/not in基本差别很小了,cbo会转为等价的写法。他们会返回所有满足条件的主表数据,不管是否有重复。

    如果主表不存在重复数据,minus和not exists结果是可以相互转为等价的,但是因为有排序,性能可能要差点,当然排序是一般情况下有,如果全部能从索引获取什么的,可能会消除。

    还有种是外连接写法,也可以转成和anti join等价的结果
    select a.* from a,b where a.id=b.id(+) and b.id is null;
    有时候not exists,not in什么的走不了anti join,也可以考虑这种写法,join在oracle里高效的算法是比较多的
    展开全文
  • MINUS,外连接,NOT IN,NOT EXISTS 的效率比较 分类: Oracle2011-06-24 11:03 415人阅读 评论(0) 收藏 举报 关于 MINUS,外连接,NOT IN,NOT EXISTS 的效率比较,绝对是一个很老很老的话题了. 如果要...

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

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

     

    展开全文
  • Oracle Minus

    2016-04-20 17:48:06
    Oracle Minus关键字  SQL中的MINUS关键字  SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果 中。如果有的话,那这一笔记录就被...

    Oracle Minus关键字
      SQL中的MINUS关键字
      SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果 中。如果有的话,那这一笔记录就被去除,而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内,那这 笔资料就被抛弃,其语法如下:
      [SQL Segment 1]
      MINUS
      [SQL Segment 2]
    --------------------------------------------
    //创建表1
    create table test1
    (
     name varchar(10),
     sex varchar(10),
    );

    insert into test1 values('test','female');
    insert into test1 values('test1','female');
    insert into test1 values('test1','female');
    insert into test1 values('test11','female');
    insert into test1 values('test111','female');

    //创建表2
    create table test2
    (
     name varchar(10),
     sex varchar(10),
    );
    insert into test1 values('test','female');
    insert into test1 values('test2','female');
    insert into test1 values('test2','female');
    insert into test1 values('test22','female');
    insert into test1 values('test222','female');
    -------------------------------------------

    select * from test1 minus select * from test2;

    结果:

    NAME       SEX            
    ---------- ---------- 
    test1      female             
    test11     female  
    test111    female 
    -----------------------------------------------------------

    select * from test2 minus select * from test1;

    结果:

    NAME       SEX            
    ---------- ---------- 
    test2      female             
    test22     female  
    test222    female

    结论:Minus返回的总是左边表中的数据,它返回的是差集。注意:minus有剃重作用

    ==========================================================
     下面是我做的实验,很明显能够看出MINUS的效率,made_order共23万笔记录,charge_detail共17万笔记录

    性能比较:
      SELECT order_id FROM made_order
      MINUS
      SELECT order_id FROM charge_detail
      1.14 sec
      
      SELECT a.order_id FROM made_order a
      WHERE NOT exists (
       SELECT order_id
       FROM charge_detail
       WHERE order_id = a.order_id
       )
      18.19 sec
      
      SELECT order_id FROM made_order
      WHERE order_id NOT in (
       SELECT order_id
       FROM charge_detail
       )
      20.05 sec
      
      还有其它一下关键字:
      INTERSECT (交集)
      UNION ALL 并集

    展开全文
  • http://www.itpub.net/viewthread.php?tid=984425&amp;extra=&amp;page=3 minus: [ CODE: Select *From gal_voucher_item itemWhere item_iid In (Select item_iid From ...
  • 关于 MINUS,外连接,NOT IN,NOT EXISTS 的效率比较,绝对是一个很老很老的话题了. 如果要完成这个需求:"取出一些记录,在表A中,不在表B中", 你会采用哪种方案?为什么会采用这种方案? 我作了一个实验, ...
  • oracle minus用法

    2019-01-07 16:58:02
    Oracle Minus关键字 SQL中的MINUS关键字 SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果 中。如果有的话,那这一笔记录就被去除...
  • Oracle Minus关键字

    2018-01-10 10:23:00
    Oracle Minus关键字SQL中的MINUS关键字SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果 中。如果有的话,那这一笔记录就被去除,而...
  • SQL中MINUS的用法

    2021-03-24 17:23:17
    minus 指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果,然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话,那这一笔资料就被去除,而不会在最后的结果中出现。如果第二个 SQL 语句所...
  • Oracle Minus 取差集

    2013-12-10 13:56:36
    Oracle Minus关键字  SQL中的MINUS关键字  SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果中。如果有的话,那这一笔记录就被...
  • Oralce MINUS 关键字

    2013-08-18 13:20:00
    Oracle Minus关键字  SQL中的MINUS关键字  SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果中。如果有的话,那这一笔记录就被...
  • [minus] xCaxMn1 [minus] yByO3 [minus] [delta] 相结构和晶格参数的影响 简短介绍 氧化学计量比对混合导电钙钛矿 Sr1 xCaxMn1 yByO3 相结构和晶格参数的影响δ F. Müller, R. Kriegel *, Ch. Kaps Bauhaus ...
  • 数据库中minus的用法

    千次阅读 2017-07-27 10:18:01
    minus指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果,然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话,那这一笔资料就被去除,而不会在最后的结果中出现。如果第二个 SQL 语句所...
  • Oracle Minus关键字 不包含 取差集

    千次阅读 2016-09-08 17:28:09
    Oracle Minus关键字  SQL中的MINUS关键字  SQL中有一个MINUS关键字,它运用在两个SQL语句上,它先找出第一条SQL语句所产生的结果,然后看这些结果有没有在第二个SQL语句的结果 中。如果有的话,那这一笔记录就被...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 13,394
精华内容 5,357
关键字:

minus效率