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

"peter" <p_msantos_at_yahoo.com> wrote in message news:1132383166.310808.77440_at_g47g2000cwa.googlegroups.com...
> So the cost makes sense..
>
>
> I'm still having some difficulties calculating the cardinality though.
> This is probably since I have a histogram on the product_id column.
> I've checkout
> chapter 7 on histograms, but can't make the numbers add up.
>
> This is what's currently in my user_tab_histograms view. There
> are only 2 values for product_id in that table.
>
> ENDPOINT_NUMBER|ENDPOINT_VALUE
> ---------------|--------------
> 1354519| 430657811
> 1943369| 438075481
>
> if you have a moment, any additional hints would be greatly
> appreciated.
> I think I'm still not sure of what are the standard rules for
> calculating the
> cardinality on columns with frequency histograms.
>
> thanks again
> --peter
>

The result depends on the predicate,
which includes considerations of whether you have a type mismatch, or whether
you have applied a function to it.

Are the product types real numeric
types with the values that show up
in the histogram.

If your predicate is simply:

    column = {actual recorded value}
then I would expect the relevant count
to be correct. In the case of a frequency histogram, the endpoint_value records your actual values, the endpoint_number records the cumulative count up to that value. So you appear to have:

    1354519 rows with the value 430657811 and

    1943369 - 1354519 rows =
    588850 rows with the value 438075481

There are anomalies if the column stats or table stats get out of synch with the histograms - which is probably only going to happen if you do unusual things with the analyze command or dbms_stats package.

You also have to question the cardinality when you use explain plan to check the cardinality of a query that uses a bind variable - as (a) the bind type is unknown, and (b) an actual value is unknown so Oracle uses the num_distinct, num_nulls, and num_rows. (And in some cases - version dependent - uses the density rather than the num_distinct).

-- 
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