ITPub博客

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

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

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

2-3


-- Oracle 9.0.1                                                                

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

  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=50010 Card=1 Bytes=2          

          6)                                                                   

   1    0   SORT (AGGREGATE)                                                   

   2    1     NESTED LOOPS (SEMI) (Cost=50010 Card=50000 Bytes=1300000         

          )                                                                    

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

          (Cost=10 Card=50000 Bytes=650000)                                    

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

          ost=1 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=50010 Card=1 Bytes=2          

          6)                                                                   

   1    0   SORT (AGGREGATE)                                                   

   2    1     NESTED LOOPS (SEMI) (Cost=50010 Card=50000 Bytes=1300000         

          )                                                                    

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

          (Cost=10 Card=50000 Bytes=650000)                                    

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

          ost=1 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=1002 Card=1 Bytes=26         

          )                                                                    

         1    0   SORT (AGGREGATE)                                                   

   2    1     NESTED LOOPS (SEMI) (Cost=1002 Card=1001 Bytes=26026)            

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

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

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

          ost=1 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=1002 Card=1 Bytes=26          

          )                                                                                                                                  

   1    0   SORT (AGGREGATE)                                                   

   2    1     NESTED LOOPS (SEMI) (Cost=1002 Card=1001 Bytes=26026)            

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

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

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

          ost=1 Card=70001 Bytes=910013)                                        

                                                                                

-- 可以看到,在本例中, 10.1.0/9.2.0CBO不管外部表是大表还是小表,使用的是in还是exists选择的执行计划都一致,均使用效率更高的HASH JOIN(SEMI)连接。而901则使用NESTED LOOPS (SEMI)连接。同时从执行计划中的COST计算来看,10g9.2.09.0.1均进步了不少,效率提高了很多。

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

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

注册时间:2007-12-28

  • 博文量
    1388
  • 访问量
    3837794