Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
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
![]() |
![]() |