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

"peter" <p_msantos_at_yahoo.com> wrote in message news:1132689391.937237.44530_at_g49g2000cwa.googlegroups.com...
> 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
>

As far as frequency histograms go when
you do column = {popular constant} that's about it, although I happen to view it from a slightly different perspective to get to the same conclusion, viz:

    table cardinality = X
    final histogram figure is Y
therefore derive count for the required value from the histogram and multiply by X/Y.
Obviously it's the same arithmetic, it's just the interpretation that's different.

Bear in mind that if you have nulls in the column, the 'original cardinality' is the num_rows for the table; so you actually have to factor the number of rows with nulls in that column into the equation, so it's 'user_tables.num_rows - user_tab_columns.num_nulls' that you need to use as the multiplier.

(I made a comment in my previous note about table and column data getting out of sync - I overlooked the very simple, and eminently reasonable, case of collecting one sample size on the table and another for the histogram; or even the fact that a sample that covers table and histogram adopts different strategies for how they store their results.)

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005