Re: Tuning: index redundance?

Thank you very much, Martin, it is an excellent reply. It is indeed strange since b is the leading column in b + c + a concatenated index, if a query use b and c in two limiting equal conditions, this index should be used, although the c condition could be taken as the leading coloun in the c + a concatenated index. The optimizer might get confused by which one, b or c, as leading column so decide whether index of b + c + a, or of c + a, should be used.

Now take your expample furture, What if we used to have 3 indexes, namely:

1. column a is a unique index
2. columns b is a unique index
3. columns a + b + c is a unique index

Case 1. if we have a query: "select * from table x where a = 123", I assue index 1 will be used if the optimizer is not acting strangely as indicated in your example.

Case 2. if we have a query: select * from table x where b = 567, index 2. will be used, most likely.

Case 3: if we have a query: "select * from table x where a = 123, and b = 567", I assue the concatenated index 3. will be used.

  Now let me delete index 1 (I might not want to delete 2. since case 2 might exist), keep concatenated index of columns a + b + c, then will that hurt in any case? I mean, first of all, will case 1 automatically use index 3 since index 1 is deleted? Secondly, if yes, is using index 3 less efficient than using index 1 (performance issue) in this case, since the query involves only column a, while the concatenated index covers columns a, b, and c?

  The second quesiton is a little complicated by the fact that I use "select *....", in some cases, if data I want is exactly those from column a, b, c, then using the concatenated index is surely better.

  Thank you again.

  Yu

On Wed, 23 Feb 2000, Martin Haltmayer wrote:

> 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 Wed Feb 23 2000 - 10:30:38 CST