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.0的CBO不管外部表是大表还是小表,使用的是in还是exists选择的执行计划都一致，均使用效率更高的HASH JOIN(SEMI)连接。而901则使用NESTED LOOPS (SEMI)连接。同时从执行计划中的COST计算来看，10g和9.2.0比9.0.1均进步了不少，效率提高了很多。