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