Re: optimization required

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 26 Feb 2008 21:27:01 -0600
Message-ID: <sK4xj.58954$Pv2.17372@newssvr23.news.prodigy.net>


Nicsmart wrote:
> I executed the following query in toad
>
> SELECT DISTINCT a.prv_cod
> FROM iw_rle_prv a, iw_prv_mst b
> WHERE a.rle_cod IN ('CEO')
> AND a.prv_cod = b.prv_cod
> AND (b.prv_cre_status = 'N' OR b.prv_cre_status IS NULL)
> AND a.del_ind = 'N'
> AND b.del_ind = 'N'
>
>
> Explain plan for the above query is
> SELECT STATEMENT Optimizer Mode=ALL_ROWS68 6.55350208921878
> HASH JOIN 68 2 K 6.55350208921878
> TABLE ACCESS FULL IWISEPRDN.IW_RLE_PRV 73 1 K 3.03524459882865
> TABLE ACCESS FULL IWISEPRDN.IW_PRV_MST 199 2 K 3.01541637294416
>
> number of records in table iw_rle_prv is 991
> and iw_prv_mst is 251
>
> whats the reason for the above query to show such high cost?Any
> suggestions to reduce it

because the sky is blue? With out much more information that is a pretty good guess..

Because you are doing FTS on both tables, my guess would be that you have no index on rle_cod (stand-alone index or leading edge of other more relevant indices) where it can do direct lookups so it resorts to a FTS. Also, if you know the value of WHERE a.rle_cod IN ('CEO') - IIRC), it might be more efficient to use "a.rle_cod = 'CEO'". (that discussion is hard to find because search engines ignore the noise word "IN"). I believe at one time, using IN does an implicit pseudo "select col from table where col = 'CEO' or col= 'XYZ' or col= 'WHATEVER'". But that could have changed over the years. Received on Tue Feb 26 2008 - 21:27:01 CST

Original text of this message