Re: Statistics madness

The CBO has a number of "sanity" overrides in its "formulas"

1. If your predicate falls outside the lo-hi range it (may) substitute defaults for the selectivity in place of the usual formula
2. the selectivity of any range predicate is bounded by the equality selectivity - kind of makes intuitive sense, a (valid, i.e. within lo-hi) range ought to return at least as many rows than any particular (valid, i.e. within lo-hi) value. The range "formula" needs to be augmented:

selectivity = max( {1/NDV | density }, (hi-value)/(hi-low) )

(see also my paper on "Fallacies of the Cost Base Optimizer)

Barr, Stephen wrote:

> Oracle 10.1.0.2.0 64bit
> Solaris 8
>
>
> I'm having trouble getting the formula's in Wolfgang's document to match up
> to the actual values I'm seeing returned by explain plans...specifically the
> formula's involving the high and low values.
>
> What am I doing wrong here?
>
>
> But it doesn't seem to be doing this - it seems to be just applying the
> density again as in the equality predicate.....what am I missing?
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l