Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: 10g CBO and how to determine cardinality on INDEX_FFS?

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

From: Jonathan Lewis <>
Date: Mon, 21 Nov 2005 12:44:24 +0000 (UTC)
Message-ID: <dlsff8$m69$>

"peter" <> wrote in message
> 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.
> ---------------|--------------
> 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).


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Cost Based Oracle: Fundamentals
Public Appearances - schedule updated 4th Nov 2005
Received on Mon Nov 21 2005 - 06:44:24 CST

Original text of this message