Re: Tuning: index redundance?

It depends on your Oracle version.

We found a strange behaviour with indexes in 7.1.6, 7.3.4 and 8.0.5 when you have the following scenario:

column a is a unique index

columns c + a is a unique index
columns b + c + a is a unique index
columns c + a is a unique index

So all the (normally non-unique) indexes were made unique artificially (the reason for that was that the db design was done on C-ISAM. It takes eternities under some circumstances if you delete from a non-unique index in C-ISAM!)

Now consider a query that has an equal condition on b and on c. You would expect that index b + c + a is used. But this was the crux: rule-based could not distinguish because all indexes were unique and none of the conditions involved all three a, b and c! So RBO picked the index that was created last.

CBO, on the other hand, could not distinguish either between the indexes had the same amount of distinct entries (or nearly the same). So if we did estimate statistics it was pure chance which of them got the most distinct values. Also creation of histograms did not help.

In 8.1.5, however, I could not reproduce any of these problems. An artificially unique index does not confuse the optimizer. But here we have to do compute statistics as Oracle Support told us there is a problem with estimate statistics (would sometimes raise ORA-00600).

Martin

Yu Dong wrote:
>
> Hey,
>
> If a column is a leading column of a concantenated index (with other
> some columns), in what condidtion would it be a good idea to create
> another one column index on this column?
>
> The simple version of the quesiton is: is this one column index on this
> column redundant given that it is already the leading conlumn of a
> concantenated index (so we can delete the one-column index)?
>
> The complex version of the question is, is there any case that makes the
> addtional index on this column useful, or effecient, or convenient? One
> consideration is maybe sometime using the one column index on this column
> is faster or whatever than using the concantenated index if it cover many
> columns?
>
> Wish to get an education here. I guess it makes more sense to have a
> separate single index on a column although this column is part of a
> concantenated index but not the leading columns of the index.
>
> Thanks in advance!
>
> Yu
Received on Tue Feb 22 2000 - 19:47:57 CST