Re: multiple indexes or a composite index?

One way to alleviate outsmarting the Optimizer and deciding whether to create composite indexes is to use OMNIDEX Multidimensional Indexes. See http://www.mdi.html or contact me for more information.

Cheryl Grandy
cgrandy_at_disc.com
303 444-4000
www.disc.com

In article <39af263a.342573534_at_nsw.nnrp.telstra.net>,   steve.adams_at_ixora.com.au (Steve Adams) wrote:
> 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
> >> ---------------------------------------------------------
> >
> >
>
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.