Re: 10g CBO and how to determine cardinality on INDEX_FFS?

The column in question is a real number column, and the predict is "where product_id = 430657811".

I understand your arithmetic on the histogram data, but it seems to be that
the histogram data is only based on the sample size, and the sample size from USER_TAB_COL_STATISTICS.SAMPLE_SIZE = 1943369.

So out of the 1.9 million sample size 500K+ records have the product_id=438075481, while the 1.3 million have the product id of interest 430657811.

So does oracle just use simple percentages to project the cardinality of a table/index based on the
selectivity of the histogram?

For example:
The histogram sample size was 1,943,369. Of those records, the product_id
of interest (430657811) was found 1,354,519 times. The other product_id was found 588850.

selectivity = 1354519 * 100 / 1943369 = 69.699527 So the selectivity of my product_id (430657811) is at about 69%.

When I multiply that percentage * orig cardinality I get 6,772,595 .. which is in the 10053 is the rounded cardinality value.

rounded cardinality = 69.699527 * 9716845 / 100 = 6,772,595

Is this how the frequency histograms are used by the optimizer? To simply come up with a selectivity ratio which is then applied to the total number of rows of the index/table?

thanks for all the great information.
-peter Received on Tue Nov 22 2005 - 13:56:31 CST