RE: Density calculation. Was: Incorrect cardinality estimate

In the other words Oracle is using histogram in the best way it can:

• lower bound of cardinality(frequency) is known only for popular values
  (because of consecutive buckets)
  
• for all the other values Oracle may only guess. It can occupy the whole bucket or just a fraction of it. Then density(derived from average frequency adjusted by frequency deviation as pointed out by Jonathan Lewis) and cleared from popular values is a decent guess for actual cardinality.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling

For a height-balanced histogram the (non-null) column values are sorted and then "filled" into buckets of num_non_null_rows/buckets rounded
(most likely up) to an integer. Then the first value, the highest value
in each bucket, and the last value are recorded. Popular values are those that occur as the highest value in more than one (consecutive) bucket.
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer

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