Re: multiple indexes or a composite index?

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 - 14:01:46 CDT