Re: SQL tuning nightmare - db file sequential reads

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