# Re: Help me solve this cardinality puzzle

• From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
• To: kutrovsky.oracle@xxxxxxxxx
• Date: Wed, 16 Apr 2008 07:47:37 -0500

```What version are you playing with? Since I know you have been around the
block a few times, I assume you know that density is usually num_rows/ndv
for FREQUENCY histograms, not HEIGHT BALANCED. =) The HEIGHT BALANCED
density calculation is a bit more tricky, and fine folks like Jonathan
Lewis, Wolfgang Breitling and Alberto Dell'Era (and many others) have
attempted to bring Oracle's formula down to earth for us mere mortals. I do
not claim to know that formula all that well yet, so I defer to others.

Not to mention the various bugs and special exceptions which can throw a
wrench into the works.

On Wed, Apr 16, 2008 at 7:15 AM, Christo Kutrovsky <
kutrovsky.oracle@xxxxxxxxx> wrote:

> I've been trying to figure this out all morning.
>
> I have a table with 20 or so columns. 2 Of those columns are
> NUMBER(10) not null and are indexed each with it's own one column
> index. Both have HEIGHT BALANCED histograms with 254 values. Both have
> computed DENSITY that is way different then num_rows/ndv.
>
> For a query of the type:
>
> select * from table where col1 = :b1
>
> CBO uses num_rows/ndv
>
> for the query of the type
>
> select * from table where col2 = :b2
>
> CBO uses density
>
> I am puzzled here ... what am I missing?
>
> Table stats:
> NUM_ROWS        3613399
> BLOCKS  1023556
> EMPTY_BLOCKS    0
> AVG_SPACE       0
> CHAIN_CNT       0
> AVG_ROW_LEN     63
>
> (columns names changed)
>
> col1:
> NUM_DISTINCT    3460
> LOW_VALUE       80
> HIGH_VALUE      C403252106
> DENSITY 1.7228608595956E-5
> NUM_NULLS       0
> NUM_BUCKETS     254
> LAST_ANALYZED   2008-Apr-16 05:39:26
> SAMPLE_SIZE     3613399
> GLOBAL_STATS    YES
> USER_STATS      NO
> AVG_COL_LEN     3
> HISTOGRAM       HEIGHT BALANCED
>
> col2:
> NUM_DISTINCT    102775
> LOW_VALUE       C5035A450E3C
> HIGH_VALUE      C50419610363
> DENSITY 0.00077343180803863
> NUM_NULLS       0
> NUM_BUCKETS     254
> LAST_ANALYZED   2008-Apr-16 05:39:26
> SAMPLE_SIZE     3613399
> GLOBAL_STATS    YES
> USER_STATS      NO
> AVG_COL_LEN     7
> HISTOGRAM       HEIGHT BALANCED
>
> Any ideas?
>
> --
> Christo Kutrovsky
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Charles Schultz
```