Re: Tuning: index redundance?

Comments inline.

Yu Dong wrote:
>
> 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?

Yes, because the index 3 has to store more data. You may need more physical disk accesses for everything, not to forget updating.

> I mean, first of all, will case 1 automatically use
> index 3 since index 1 is deleted?

You can hope that but it depends on your optimizer and your db version. If no else condition applies RBO should pick that index for a range scan. But anything else depends on your optimizer, on your statistics and your db version. If a alone is not very selective (and the optimizer before 8.1.x cannot tell if you make this index artificially unique by concatenating with b) anything else but a range scan may be picked by the CBO. It also depends on your optimizer_goal.

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

I would say yes because you have to process more data (a, b, c compared to a alone). As a consequence you may have more physical disk accesses, at least when updating a, b or 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.

Again this depends. See above.

>
> Thank you again.
>
> Yu

Martin

>
> 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:52:29 CST