RE: wrong cardinality

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Christian.Antognini@xxxxxxxxxxxx
  • Date: Mon, 13 Feb 2006 19:14:54 -0700

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 

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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2006