Forum Stats

  • 3,733,158 Users
  • 2,246,705 Discussions
  • 7,856,540 Comments

Discussions

Higtogram calculation

Hello Experts,

I am reading a white paper about CBO statistics the link is here. http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-optimizer-stats-concepts-110711-1354477.

In the part of Frequency Histograms, in number 4. It gives a formula that how optimizer calculates the cardinality when use frequency histograms.

the Optimizer would first need to determine how many buckets in the histogram have 10 as their end point. It does this by finding the bucket whose endpoint is 10, bucket 503, and then subtracts the previous bucket number, bucket 483, 503 - 483 = 20.


After that the pharagraph continues like below

Then the cardinality estimate would be calculated using the following formula (number of bucket endpoints / total number of bucket) X NUM_ROWS, 20/503 X 503, so the number of rows in the PROMOTOINS table where PROMO_CATEGORY_ID =10 is 20.


My question is, when optimizer subtracts the previous bucket number from the intended bucket number. In that example, the result is 503 - 483 = 20. So, cant we already find the cardinality? I don't understand that why optimnizer needs the following formula? At least, can somebody explain why?

(number of bucket endpoints / total number of bucket) X NUM_ROWS



At the same time, If you look at Oracle documentation here Histograms


The end points show different location. For example, in white paper the end point and the value is same. However, in documents end point the the bucket number. Basicly the concept of histogram is very simple but documents make it confusing. Please share your remarkable thoughts.



Thanks in advance.

This discussion has been closed.