RE: Help me solve this cardinality puzzle

Christo, it depends on what your bind values are and if those values are popular values according to the histogram or not. Jonathan Lewis's famous book has information on this on the chapter related to histograms.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christo Kutrovsky Sent: Wednesday, April 16, 2008 3:16 PM
To: oracle-l
Subject: 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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 16 2008 - 08:18:03 CDT