V6.0.33 bug on indexed retrievals??

• Quoting Carlos_at_Rustle.Demon.Co.Uk to All dated 04-07-94 ***
  > Symptom: Indexed queries of a large table suddenly take 10
  > times as long as usual.
  Yes, I have run across this at several client installations...

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

I can probably explain -- you might be looking in the wrong place. The optimizer in Oracle 6 is syntax-based, i.e., any decisions other than syntactical ones are arbitrary. Therefore, if there is a situation where it is possible to use one of several indexes, an arbitrary one will be chosen.

Let me provide a more concrete example: you have table a and b which are joined through a.a=b.b. b contains 400,000 rows, and a contains 40 rows. You create an index on each primary key.

There are two possible routes which can be taken -- either a can be the master, or b can. The choice will be arbitrary, because the optimizer can choose from either index. Performance will suffer greatly if b is the master.

If you drop the index on a, performance will increase! The reason is that the optimizer will then decide to use the non-indexed table as the master. If you drop both indexes and recreate them, it will be a random shot which table will be selected first, though it will remain consistent until the indexes are dropped and created.

Conclusion: your performance problems may be caused by an index in a different table's -creation-. My suggestion is that you look to other tables for the source of the performance problem. Also, you may wish to force the master relationship by using trivial functions in join conditions. For example:

select a,b
  from a,b
 where nvl(a,'')=b;

Will turn off the index on a. Received on Sun Apr 10 1994 - 12:59:01 CEST