Re: Performance / Index

Terry Ball wrote:
>
> Nuno Souto wrote:
>
> > On Wed, 12 Apr 2000 17:26:54 +0100, Duarte Nuno Sousa
> > <Duarte.Sousa_at_solsuni.pt> wrote:
> >
> > >I have already re-analyzed the tables and the indexes, but with the estimate
> > >statistics, or should i use the compute statistics ?
> >
> > Hmm, I use compute evey time I can. But you might get away with
> > estimate. Use a reasonable percentage, not 10%. Something like 50.
> >
>
> If you use a percentage of 50, oracle will automatically do a compute so
> if you are going to use 50%, you might just as well compute.
>
> Mike Ault (who presents at IOUG and other places, and works for
> RevealNet) says he did extensive testing on this. He has proven to
> his satisfaction, that a sample of 30% is best. It gives more that
> adequate numbers with the least time. A smaller percentage does
> not give as close results and a higher percentage does not give any
> more accurate figurs while taking longer.
>
> Terry

Nice example I had recently:

estimate	- num_distinct for a column gave 120
estimate 20%	- num_distinct for a column gave 1000
compute		- num_distinct for a column gave 1,100,000

in a 10,000,000 row table. This made a major difference to the quality of optimiser decisions - although the compute took a l o n g time to run

:-)

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse