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

On 1 Jul 2004 12:14:09 -0500, colin_lyse_at_yahsd.com (colin_lyse) wrote:

>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.

Physical reads is an irrelevant measure, and flushing the SGA. You should look at consistent gets. Currently you are trying to tune your buffer cache, you are not trying to tune your statement, as you are looking at an incorrect indicator.
You should also do away with your upper() on the lefthand side of all your conditions in the where clause of your index, as the likely result is you can't use an index anymore, as the full table scan on lm_exception_list is actually showing you. Also you should replace the b.id in your subquery by 'x'. If your b.id doesn't occur in an index, the table will be read. If you are using 'x', the subquery will just read the index. In CBO, using the /*+HASH_AJ*/ hint on not in subqueries should help.  Maybe you should also very optimizer_index_caching and optimizer_index_cost_adj. Their default values will result in weird execution plans. Optimizer_index_caching should be set to your cache hit ratio.

--
Sybrand Bakker, Senior Oracle DBA