wiz@telesis.COM (Karl Schendel) said: 
 
> - The optimizer is not perfect, and can be fooled.  We've run into a 
>    few situations (not easily characterized) where keeping statistics on 
>    a column fooled the optimizer into picking a sub-par query plan. 
>    In one of the cases of this sort that I can remember, it was partly 
>    due to a really crappy table key design, but then these things happen 
>    in real life... 
 
What I've found is that when the optimizer messes up, it's usually been
denied some information. One of my personal favorites is the noduplicates
or unique left out of the table definition, followed by complaints that the
optimizer took the long way around. For all it knows, that exact match
yeilds 10% of the table size (the default) not the one row the humans know
is out there. 
 
 
> I have tended to take the approach of not doing optimizedb on any column (not  
> even key columns) unless I can find a query in which it will make an observable  
> improvement. 
 
That's quite a step back. I've tended to get aggressive about table, key,
and index design, then collect statistics on anything that might be
interesting. 
 
Especially with "repeated" queries and in database procedures, statistics
can be a huge performance gain. 
 
 
> Where optimizedb tends to give the biggest wins is for flag or status columns
> with just a few values and highly skewed distributions. 
 
And at the other end, for a large table with very few rows of any
particular value. In both these cases the default assumptions about the
data distribution are off, which is what causes the problem. 
 
 
James Bullock - Rare Bird Enterprises 
620 Park Avenue # 171 
Rochester, NY. 
716-242-4824 
716-244-9072 (Fax) 

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William