ITPub博客

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

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

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

-- Oracle 9.2.0

test@GZSERVER>

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

  2  /

 会话已更改。

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

  2  /

   COUNT(*)                                                                     

----------                                                                      

     20001                                                                     

 Execution Plan

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

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

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (SEMI) (Cost=152 Card=50000 Bytes=1300000)             

   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)                                   

                                                                               

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

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

  3  /

 

  COUNT(*)                                                                      

----------                                                                     

     20001                                                                     

 Execution Plan

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

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

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (SEMI) (Cost=152 Card=50000 Bytes=1300000)             

   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)                                  

 

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

  2  /

 

  COUNT(*)                                                                     

----------                                                                     

      1001                                                                      

Execution Plan

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

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

   1    0   SORT (AGGREGATE)                                                    

   2    1     HASH JOIN (SEMI) (Cost=31 Card=1001 Bytes=26026)                 

   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)                                  

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

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

  3  /

 

  COUNT(*)                                                                     

----------                                                                      

      1001                                                                     

Execution Plan

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

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

   1    0   SORT (AGGREGATE)                                                   

   2    1     HASH JOIN (SEMI) (Cost=31 Card=1001 Bytes=26026)                 

   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)                                   

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

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

注册时间:2007-12-28

  • 博文量
    1157
  • 访问量
    3625476