Help: Why does a query go from 4 secs to 2 hrs due to using NOT IN instead of NOT EXISTS

using Oracle9i Enterprise Edition Release 9.2.0.3.0 on Sun Unix Starfire server running SUN OS.

have 3 tables

LM_COMP_SUMMARY_ARCHIVE - 60,000 rows, 27 columns, avg row length 120 bytes, 9 megs in size. has a PK index.

lm_mel - 7,000 rows 15 columns 221 bytes

LM_EXCEPTION_LIST - 36 rows 4 columns

we are updating the LM_COMP_SUMMARY_ARCHIVE table based on:
- value on two columns one of them part of the primary key index

• AND part of the primary key not being in the join of the lm_mel and lm_exception tables

All tables were totally analyze using DBMS STATS compute statistics on table and indexes

the following statement took 2.5 hours to run this morning. it did 42,000,000 physical reads

statement:

UPDATE lm_comp_summary_archive dd

   SET cat_a = 9
 WHERE maxphs_a < 0
   AND load_date = TRUNC(sysdate - 1)
   AND compoment_id_sq NOT IN (

          SELECT b.ID
            FROM lm_exception_list a, lm_mel b
           WHERE UPPER (a.station) = UPPER (b.stanum)
             AND UPPER (a.component) = UPPER (b.equip)
             AND (UPPER (exception_type) = 'Z' OR UPPER (exception_type) = 'A'
                 ))

explain plan:

UPDATE STATEMENT Optimizer Mode=CHOOSE          1               7              
                         
  UPDATE        LM_COMP_SUMMARY_ARCHIVE                                        
                  
    TABLE ACCESS BY INDEX ROWID LM_COMP_SUMMARY_ARCHIVE 1       18      7      
                                  
      INDEX RANGE SCAN  LM_COMP_SUMM_PRIM       9               2              
                          
        NESTED LOOPS            1       28      19                             
          
          TABLE ACCESS FULL     LM_EXCEPTION_LIST       1       13      2      
                                  
          TABLE ACCESS FULL     LM_MEL  1       15      17                     

we rewrote is as a not exists as follows and it took 4 secs (after we flush the SGA) and did 80,000 physical reads:                 

UPDATE /*+ CACHE(LM_COMP_SUMMARY_ARCHIVE) */lm_comp_summary_archive dd

   SET cat_a = 9
 WHERE maxphs_a < 0
   AND load_date = TRUNC(sysdate -1)
   AND NOT exists (

          SELECT b.ID
            FROM lm_exception_list a, lm_mel b
           WHERE b.id = dd.compoment_id_sq
                     and UPPER (a.station) = UPPER (b.stanum)
             AND UPPER (a.component) = UPPER (b.equip)
             AND (UPPER (exception_type) = 'Z' OR UPPER (exception_type) = 'A'
                 ))

plan:

UPDATE STATEMENT Optimizer Mode=CHOOSE          1               10             
                          
  UPDATE        LM_COMP_SUMMARY_ARCHIVE                                        
                  
    TABLE ACCESS BY INDEX ROWID LM_COMP_SUMMARY_ARCHIVE 1       18      10     
                                  
      INDEX RANGE SCAN  LM_COMP_SUMM_PRIM       12              3              
                          
        NESTED LOOPS            1       28      4                              
          
          TABLE ACCESS BY INDEX ROWID   LM_MEL  1       15      2              
                          
            INDEX UNIQUE SCAN   LM_MEL_UNIQUE   7 K             1              
                          
          TABLE ACCESS FULL     LM_EXCEPTION_LIST       1       13      2      
                                  

what is really confusing is that the plans looks very similar. the only difference is in how the lm_mel and lm_exception tables are assessed and the first one would be the one i would pick since lm_exception is only 36 rows.

can somone tell me why such a dramatic difference 2.5 hrs vs. 4 secs? with such a minor difference?

Also I added a hint to do a full table scan on LM_COMP_SUMMARY_ARCHIVE with the the first statement using not in and it ran in 2 secs. Received on Thu Jul 01 2004 - 12:14:09 CDT