Re: SQL tuning nightmare - db file sequential reads

Mladen Gogala wrote:
> On Tue, 04 Jul 2006 08:02:03 -0700, joshic75 wrote:
>
> >
> > Could anybody please help in explaining this behaviour and suggest a
> > possible remedy.
>
> What you have shown is consistent with the reasonable expectation. You are
> doing single block reads aka "db file sequential read" and your plan shows
> that you're using the index. Unfortunately, you're accessing only indexes,
> as the consequence of the /*+ index_ffs(s_asset, s_asset_bt_w1_x) */ . Get
> rid of that hint and you will probably perform fewer db file scattered
> read events, also known as "multiblock reads". In the end, multiblock
> reads should be faster. Your INDEX_FFS hint was probably a trick to make
> "NOT EXISTS" predicate work faster. The "NOT EXISTS" can sometimes be
> re-formulated as minus, which I find easier to understand and optimize.
>
> --
> http://www.mgogala.com

This depends on the data. A MINUS will almost always full scan at least one table in the query being "minused". An EXISTS will run once for each row returned by the outer query (33,000 times in this case). Depending on how much data is in S_ETL_R_IMG_6, it may be better or worse. If S_ETL_R_IMG_6 is small, it will be better, if large...

Regards,

Steve Received on Tue Jul 04 2006 - 12:43:47 CDT