ITPub博客

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

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

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

         3) 各个版本下CBONOT INNOT EXISTS的不同

假定各实验表在id列上均有not null约束。

test@GZSERVER>

test@GZSERVER> prompt -- 各个版本cbo的影响

-- Oracle 10g

test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='10.1.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.05

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2         

          6)                                                                    

   1    0   SORT (AGGREGATE)                                                   

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

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

          (Cost=23 Card=50000 Bytes=650000)                                    

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

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

 Statistics

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

        385  recursive calls                                                   

          0  db block gets                                                     

        371  consistent gets                                                   

        220  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: 00.03

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2      

          6)                                                                    

   1    0   SORT (AGGREGATE)                                                   

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

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

          (Cost=23 Card=50000 Bytes=650000)                                    

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

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

 

Statistics

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

          2  recursive calls                                                   

          0  db block gets                                                     

        299  consistent gets                                                   

        119  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=ALL_ROWS (Cost=45 Card=1 Bytes=26         

          )                                                                     

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (ANTI) (Cost=45 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=42 Card=70001 Bytes=910013)                                   

 Statistics

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

        202  recursive calls                                                   

          0  db block gets                                                     

        233  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=ALL_ROWS (Cost=45 Card=1 Bytes=26         

          )                                                                     

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (ANTI) (Cost=45 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=42 Card=70001 Bytes=910013)                                  

 Statistics

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

          1  recursive calls                                                   

          0  db block gets                                                     

        199  consistent gets                                                   

          0  physical reads                                                    

                                                              

 -- 10g中不管是not in还是not exists均使用了hash反连接

-- 执行计划一样,但从统计信息来看not inrecursive calls更高并且有排序操作.

-- not exists没有排序操作是因为重用了上次not in排序产生的排序

-- (不妨先运行not exists然后运行not in,会发现not in没有排序的统计)

-- 同样的会发现recursive calls也降低了,见如下测试

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: 00.03

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2

          6)

   1    0   SORT (AGGREGATE)

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

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

          (Cost=23 Card=50000 Bytes=650000)

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

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

 

Statistics

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

          1  recursive calls

          0  db block gets

        299  consistent gets

        119  physical reads

         

 

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

m inner_large_t)

  2  /

 

  COUNT(*)

----------

     29999

 

已用时间:  00: 00: 00.03

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2

          6)

   1    0   SORT (AGGREGATE)

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

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

          (Cost=23 Card=50000 Bytes=650000)

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

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

 

Statistics

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

上一篇: 重拾java
请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1393
  • 访问量
    3840503