Re: multiple indexes or a composite index?

I just want to remind readers of this particular thread that I'm referring to a full INDEX scan
rather than a full TABLE scan.

(God help those of us who need to do a full TABLE scan)

"Steve Adams" <steve.adams_at_ixora.com.au> wrote in message news:39af263a.342573534_at_nsw.nnrp.telstra.net...
> 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 Sat Sep 02 2000 - 22:43:24 CDT