Re: V6.0.33 bug on indexed retrievals??

In article <765734249snz_at_rustle.demon.co.uk>, Carlos_at_rustle.demon.co.uk (Carlos Karels) wrote:

> Has anyone out there come across this "feature" under ORACLE V6.0.33 ??
>
> Symptom: Indexed queries of a large table suddenly take 10 times as
> long as usual.
>
> Investigations: VALIDATE INDEX performed - Index validated, no performance
> improvement.
>
> Index dropped/recreated - no performance improvement.
>
> Index dropped, data exported, table dropped/recreated, data
> imported, index recreated - Performance returns to expected
> speed
>
> Each retrieval attempt was performed twice, once with the
> TRACE option switched on. In all cases the EXPLAIN PLAN
> indicated that it was an indexed retrieval.
>
> Conclusion: The index appears to become unusable, but the Dictionary
> and the VALIDATE INDEX options are unable to recognise
> this. The fact that dropping/recreating the index does not
> clear the problem is confusing.
>
> This is not a common problem, (it has ocurred twice in 18 months), but the
> ORACLE Help desk assures me they have not had any other occurrences reported
> to them before (or since?)
>
> - Has anyone got any ideas????
>

How many indexes do you have on the table. If more than one, do both indexes have equal weight in Oracle 6's rule based optimizer eg. where clauses refer to indexes which are both unique or both composite. In such cases Oracle's choice seems somewhat random, something like: uses the index most recently created (?). This may lead to the situation that you (and I've observed), i.e. add an additional index to make a particular querry faster, and pre-existing querry runs slower!

Comments from anyone else.

We run 6.0.33.0.0 on VMS/VAX.

-- 
David Bellamy.
Deparment of Commerce.  The University of Queensland.   Australia.
Internet:   bellamy_at_commerce.uq.edu.au