Re: multiple indexes or a composite index?

Hi Steve,

I faced a situation a few hours ago when Oracle choose a full table scan despite having separate bitmap indexes on each of the predicate columns, whereas the bitmap merge was in fact a far better plan. What Oracle did not know was that there was a strong (negative) correlation between the columns such that the combined selectivity of the bitmap merge would be very good. In such situation a composite index can alter to optimizer to such correlations.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/
@   
@   Going to OpenWorld?
@   Catch the Ixora performance tuning seminar too!
@   See http://www.ixora.com.au/seminars/ for details.

On Thu, 31 Aug 2000 12:01:46 -0700, "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote:

>IMHO, composite indexes should only be used when the composite fields
>are part of an unique constraint.
>
>When using composite indexes where one (or more) of the fields comprising
>the composite is missing, Oracle performs a full index scan. If the fields
>are
>indexed separately, the use of the index is much more efficient.
>
>
>"Steven Hauser" <hause011_at_garnet.tc.umn.edu> wrote in message
>news:8oe496$ca0$1_at_garnet.tc.umn.edu...
>> As Sybrand sez:
>> >>Some general guidelines on indexes:
>> >>1 For all composite indexes: the most discriminating column must be
 leading
>>
>> I have found many badly designed composit indexes improve performance
>> by a factor of 10 when rebuilt with the first attribute having the
>> best domain (number of values and even distribution).
>>
>> This is because it works better for other queries
>> that just might use that attribute. If the "best" attribute
>> is not at the front of any index its chances of being used
>> is much less as it has to match exactly the attributes used
>> in the query where clause to the depth it is buried in the index.
>>
>> Too many indexes are designed for one query by an inexperienced developer
>> with a single-application-centric view of how a database is used.
>>
>> --
>> ---------------------------------------------------------
>> Steven Hauser
>> email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011
>> ---------------------------------------------------------
>
>
Received on Thu Aug 31 2000 - 22:49:24 CDT