Re: redundant indices?

ryanbobko_at_yahoo.com (ryan) wrote in message news:<29c46df8.0310020621.788b9c2f_at_posting.google.com>...
> Hi All,
> I've been reviewing some indices in a database I've recently become
> the DBA for, and I need some advice. On one table, we have the
> following indices (the names have been changed to protect the
> innocent):
>
> index1( AAA, XXX, YYY );
> index2( BBB, XXX, YYY );
> index3( XXX, YYY, ZZZ );
> index4( CCC, YYY, ZZZ );
> index5( DDD, YYY, ZZZ );
>
> I'm a bit less concerned with speed than I am disk space, and I was
> wondering if the following layout wouldn't be appropriate:
>
> index6( YYY, ZZZ );
> index7( XXX, YYY );
> index8( AAA );
> index9( BBB );
> index10( CCC );
> index11( DDD );
>
> I guess my question is: Can oracle approximate the performance of
> index1 by searching index8 and combining the results with a search of
> index7? Or will only index8 be used?
>
> Thanks for the advice,
> ry

Depends what you are doing, and on which version, and if you are partitioning, and how big the tables are relative to each other, and how your SGA and init.ora are set up, and whether you are hitting any bugs, and whether some other join will get screwed up by removing indices, and... Probably the best thing for you to do is to try both with an explain plan.

I guess the answer is "it could, and in some cases could be faster, since the handling of smaller keys that are sorted and merged may not blow it into temp space, or on the other hand, maybe not."

See the Tuning manual for your version, particularly the part about Choosing Columns to Index. O9 can use composite indices even without the leading value.

jg

--
@home.com is bogus.
http://www.dilbert.com/comics/dilbert/archive/dilbert-20031002.html