Re: Help me solve this cardinality puzzle

Oops, seems like I forgot a few details.

• Oracle EE 10.2.0.3
• bind peeking disabled
• purelly looking at cardinality of explain plan

What puzzles me is that for what looks like very similar columns, oracle uses different sources of information for estimating the number of rows returned.

In one case it uses num_rows * density, in other num_rows/ndv. Both have same type of histograms, same data type, same indexes.

The only logical conclusion I am making so far is some kind of sanity check causing it to fall back to use num_rows/ndv instead of density when density < num_rows/ndv.

On Wed, Apr 16, 2008 at 4:50 PM, Wolfgang Breitling <centrex_at_centrexcc.com> wrote:
> When histograms are present the cardinality calculation depends, among other
> things, on whether the predicate value (bind value in your case) is
> a) a popular value, which then of course shows up in a row in the histogram
> or
> b) a non-popular value which has a row in the histogram
> or
> c) a non-popular value which has no row in the histogram ( but is still >=
> low_value and <= high_value )
>
>
>
> Quoting Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>:
>
>
> >
> >
> >
> > 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
> >
> >
> >
> >
>
>
>
> --
> Wolfgang Breitling
> Centrex Consulting Corporation
>

-- 
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