It depends on the size of the index. It helps with the first read of
the index, then it will be cached (depending on size of course). It
also helps the optimizer to choose the index.
Mike
dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0205221448.b74840f_at_posting.google.com>...
> mikerault_at_earthlink.net (Mike Ault) wrote in message news:<37fab3ab.0205221049.1377912e_at_posting.google.com>...
> > dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0205211119.5b4b6b90_at_posting.google.com>...
> > > nsouto_at_optushome.com.au (Nuno Souto) wrote in message news:<dd5cc559.0205151535.cef9399_at_posting.google.com>...
> > > > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3ce21b71$0$8510$ed9e5944_at_reading.news.pipex.net>...
> > > > > Suggested list to be added to, deleted from etc
> > > >
> > > > Oh yeah, almost forgot:
> > > >
> > > > - Put the most selective column first in a concatenated index.
> > > > (this one AFAIK was never true, but somehow it stuck)
> > > >
> > >
> > > I found myself on the opposite side again :-)
> > >
> > > There is one reason to do this: the most selective column is more likely
> > > to be part of the criteria of many queries. Therefore having the most
> > > selective column first will likely allow you to serve many queries with
> > > one index. As for performance, compressed index with the least selective
> > > column first may be better. So do what's best for your application.
> > >
> > > >
> > > > Cheers
> > > > Nuno
> >
> > I say, determine the order of columns such that clustering factor is
> > minimized and then reorder the query, of course in 8i and later Oracle
> > can do that for you...
> >
> > Mike
>
> I've tried this technique before. After I reorganized my table to reduce
> the clustering factor by a factor of at least 50, from close to the number
> of rows to exactly the number of table blocks, my batch jobs only ran a few
> percent faster. That's far less than I expected.
>
> What kind of performance gain should one expect? I know it "depends" but
> some typical figures would really help.
Received on Tue May 28 2002 - 09:47:35 CDT
