Re: redundant indices?

Take a look at the AND_EQUAL hint in the Oracle doc, and you'll see that Oracle can in fact combine the result of multiple indices. But according to the doc, this is only for 1-column indices. Since your example involves an index with multiple columns, I'm not 100% sure. Why don't you try a test case?

Daniel

> 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
Received on Thu Oct 02 2003 - 15:46:19 CDT