Re: Help me solve this cardinality puzzle

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_at_gmail.com> 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
> DBA Team Lead
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l