Re: Oracle Myths

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 Wed May 22 2002 - 17:48:41 CDT