RE: Help me solve this cardinality puzzle
- From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
- To: <kutrovsky.oracle@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Apr 2008 16:18:03 +0300
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
