-- Oracle 9.0.1
test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='9.0.1'
  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: 01.02

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=50010 Card=1 Bytes=2         
          6)                                                                   
   1    0   SORT (AGGREGATE)                                                   
   2    1     NESTED LOOPS (ANTI) (Cost=50010 Card=1 Bytes=26)                 
   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)                                       

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
     100158  consistent gets                                                   
          0  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.06

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=13)          
   1    0   SORT (AGGREGATE)                                                   
   2    1     FILTER                                                           
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)          
          (Cost=10 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=1002 Card=1 Bytes=26   
          )                                                                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     NESTED LOOPS (ANTI) (Cost=1002 Card=1 Bytes=26)                  
   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)                                       

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       2009  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=1 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=1 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                                                    
          …                                                    

test@GZSERVER> spool off

-- 在本例中,
-- 从执行计划来看,不管外部表是大表还是小表,not in均使用了NESTED LOOPS反连接
-- not exists使用FILTER.
-- 从统计信息来看,在外部表为大表的时候,not in与not exists半斤八两,在本例中分不出明显的优劣.
-- 但通过在使用not in的同时通过加hash_aj提示,会明显的提升not in的性能.
test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='9.0.1'
  2  /

会话已更改。

已用时间:  00: 00: 00.00
test@GZSERVER> select count(*) from outer_large_t where id not in (select /*+ ha
sh_aj*/ id from inner_large_t)
  2  /

  COUNT(*)
----------
     29999

已用时间:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=150 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (ANTI) (Cost=150 Card=1 Bytes=26)
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
          (Cost=10 Card=50000 Bytes=650000)
   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
          ) (Cost=18 Card=70001 Bytes=910013)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        299  consistent gets
        119  physical reads
          …

-- 跟原来的统计信息相比较,从原来100158逻辑读变为299逻辑读，性能得到了极大的提升。