# Re: Re: Density statistic calculation in case of histogram

• From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
• To: Neeraj Bhatia <neeraj.dba@xxxxxxxxx>
• Date: Thu, 9 Apr 2009 10:21:39 +0200
```On Thu, Apr 9, 2009 at 03:19, Neeraj Bhatia <neeraj.dba@xxxxxxxxx> wrote:
> Thanks Alberto for the update. Actually I am investigating the calculations
> in R10 and my plan was to later cover R11 as well. Since still most of the
> systems are on R10 and in future also (atleast 2-3 years), I am very
> interested.

First of all, for a filter predicate "where column=constant", density is
not used for popular values. So, consider only the case of constant
being not popular, and imagine removing the popular values from the
table (which is what dbms_stats does).

Of course the estimated cardinality of a filter predicate "where
column=constant" "has to be" the expected value of the cardinality,
that is, the average value of the number of rows retrieved over all possible
values of constant.
If we knew the probability mass function (PMF) of "constant" (i.e. we could
characterize the workload perfectly), the expected value would be

sum( count(constant) * PMF (constant) ), over all values of constant

dbms_stats knows count(constant) perfectly, knows nothing about the PMF.

If we assume PMF(constant) = count(constant) / num_rows, that is,
the more frequently constant is represented in the table, the higher
the probability of being requested by the client is
=> we get the square-based "OldDensity" formula.

If we assume PMF(constant) = 1 / num_distinct, that is, we assume
that each *distinct value* has the same probability of being requested
by the client
regardless of how frequently it is represented in the table
=> we get the "NewDensity" formula (which is also the most intuitive).

Remember that the above figures must be adjusted by num_rows,
since the estimated cardinality is density * num_rows, where num_rows
is the total number of rows of the original table.

hth
Alberto

--
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l

```