RE: wrong cardinality

At 06:09 PM 2/13/2006, Christian Antognini wrote:
>Stalin
>
> >Can anyone explain why the cardinality is way off from the expected
> >2Mil mark. Below query should return a count of 2million rows. However
> >both index and full scan give 133k.
>
>Data in TYPE and DOMAINID is probably correlated.
>Since the CBO has no clue about it, it does wrong estimates...
>
>Basically, in such a filter, a formula like the following one is applied:
>
>(selectivity1+selectivity2-selectivity1*selectivity2)*selectivity3*numrows =
>(0.25+0.25-0.25*0.25)*0.06666*4000137 = 117K
>
>Quite close to 133K... some kind of correction is missing... but it
>should give you an idea on how the estimation works...

Or the data in TYPE or DOMAINID is skewed. Or a combination of all of the above.

The correction comes from the fact that Christian's formula for OR predicates is correct for predicates involving different columns. Expressing it in probability terms:

p( A or B ) = p(A) + P(B) - p(A and B)

In this case the optimizer knows that the two sides of the OR can not both be true, i.e. p(A and B) = selectivity1*selectivity2 = 0

The formula in this case [ predicate in (...) ] simplifies to

n*selectivity1 * selectivity3 * num_rows (where n is the number of terms in the in-list, probably with a heuristic cap ) = 0.5 / 15 * 4000137 = 133337.9 ~ 133K

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l