Help me solve this cardinality puzzle

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

Other related posts: