ITPub博客

首页 > Linux操作系统 > Linux操作系统 > IN/EXISTS/NOT IN/NOT EXISTS(3-3)

IN/EXISTS/NOT IN/NOT EXISTS(3-3)

原创 Linux操作系统 作者:husthxd 时间:2004-12-15 00:00:00 0 删除 编辑

IN/EXISTS/NOT IN/NOT EXISTS 3-3


--Oracle 9.2.0

test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='9.2.0'

  2  /

 

会话已更改。

 

已用时间:  00: 00: 00.00

test@GZSERVER> select count(*) from outer_large_t where id not in (select id from inner_large_t)

  2  /

 

  COUNT(*)                                                                     

----------                                                                      

     29999                                                                     

 

已用时间:  00: 00: 00.03

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=152 Card=1 Bytes=26)         

   1    0   SORT (AGGREGATE)                                                    

   2    1     HASH JOIN (ANTI) (Cost=152 Card=1 Bytes=26)                      

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)          

          (Cost=11 Card=50000 Bytes=650000)                                     

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         

          ) (Cost=19 Card=70001 Bytes=910013)                                   

 

 

Statistics

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

          2  recursive calls                                                   

          0  db block gets                                                     

        299  consistent gets                                                   

        119  physical reads                                                    

                                                              

 

test@GZSERVER> select count(*) from outer_large_t t1

  2  where not exists (select null from inner_large_t t2 where t1.id = t2.id)

  3  /

 

  COUNT(*)                                                                     

----------                                                                     

     29999                                                                      

 

已用时间:  00: 00: 01.03

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=13)          

   1    0   SORT (AGGREGATE)                                                    

   2    1     FILTER                                                           

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)          

          (Cost=11 Card=2500 Bytes=32500)                                       

   4    2       INDEX (RANGE SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX) (C         

          ost=2 Card=700 Bytes=9100)                                            

 

Statistics

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

          1  recursive calls                                                   

          0  db block gets                                                     

     100106  consistent gets                                                   

          0  physical reads                                                    

                                                              

 

test@GZSERVER> select count(*) from outer_small_t where id not in (select id from inner_large_t)

  2  /

 

  COUNT(*)                                                                     

----------                                                                     

       500                                                                     

 

已用时间:  00: 00: 00.00

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=26)          

   1    0   SORT (AGGREGATE)                                                    

   2    1     HASH JOIN (ANTI) (Cost=31 Card=1 Bytes=26)                       

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE         

          X) (Cost=2 Card=1001 Bytes=13013)                                    

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         

          ) (Cost=19 Card=70001 Bytes=910013)                                   

 

 

Statistics

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

          1  recursive calls                                                   

          0  db block gets                                                     

        199  consistent gets                                                   

          0  physical reads                                                    

                                                              

 

test@GZSERVER> select count(*) from outer_small_t t1

  2  where not exists (select null from inner_large_t t2 where t1.id = t2.id)

  3  /

 

  COUNT(*)                                                                     

----------                                                                     

       500                                                                     

 

已用时间:  00: 00: 00.00

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=13)           

   1    0   SORT (AGGREGATE)                                                    

   2    1     FILTER                                                           

   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE         

          X) (Cost=2 Card=50 Bytes=650)                                         

   4    2       INDEX (RANGE SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX) (C         

          ost=2 Card=700 Bytes=9100)                                           

 

 

Statistics

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

          1  recursive calls                                                   

          0  db block gets                                                     

       2008  consistent gets                                                   

          0  physical reads                                                    

                                                              

 

-- 在本例中,

-- 从执行计划来看,不管外部表是大表还是小表,not in均使用了效率更高的hash反连接

-- not exists还是使用FILTER操作.

-- 从统计信息来看,在外部表为大表的时候,not in要远远优于not exists

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-21670/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 3月21日
请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1148
  • 访问量
    3620292