13 Replies Latest reply: Sep 25, 2012 12:48 PM by 698658

# Cardinality linear decay in 9.2.0.8

Hi,
lets say I've got date column with increasing date (populated as sysdate) , in point of time I created index (compute statistics) on that column without histogram (only min/max value) .
So if I dont regather stats on that column, my cardinality estimate will go lower day after day till value of 1 , right (because out of linear decay) ?
Regards
GregG
• ###### 1. Re: Cardinality linear decay in 9.2.0.8
In general, Yes. There could be a decay in the cardinality estimate. The "formula" may vary between versions -- even patch levels.

For example, see :
http://hemantoracledba.blogspot.sg/2009/04/incorrect-cardinality-estimate-of-1-bug.html

Hemant K Chitale
• ###### 2. Re: Cardinality linear decay in 9.2.0.8
Also see

http://hemantoracledba.blogspot.sg/2012/09/cardinality-decay.html

Hemant K Chitale
• ###### 3. Re: Cardinality linear decay in 9.2.0.8
GregG wrote:
lets say I've got date column with increasing date (populated as sysdate) , in point of time I created index (compute statistics) on that column without histogram (only min/max value) .
So if I dont regather stats on that column, my cardinality estimate will go lower day after day till value of 1 , right (because out of linear decay) ?
I think Hemant missed your reference to 9.2.0.8. The linear decay algorithm appeared in 10.1, in 9.2.0.8 the cardinality for "column out of range" (whether using equality or between et. al.) is the same as "column = constant inside range".

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>
• ###### 4. Re: Cardinality linear decay in 9.2.0.8
Great , thanks .
Regards
GregG
• ###### 5. Re: Cardinality linear decay in 9.2.0.8
Just clicked answered , but "column = constant inside range" is that any formula ?
So You mean the cardinality is constant regardless how far we are from max value stored in statistics ?
Regards
GregG
• ###### 6. Re: Cardinality linear decay in 9.2.0.8
Aah ! "The "formula" may vary between versions -- even patch levels." So, it was quite different in 9.2.0.8

Hemant K Chitale
• ###### 7. Re: Cardinality linear decay in 9.2.0.8
You are allowed to revoke the "answered" star in 15minutes after granting it.

Hemant K Chitale

Edited by: Hemant K Chitale on Sep 25, 2012 5:11 PM
• ###### 8. Re: Cardinality linear decay in 9.2.0.8
Maybe some more comments will show up.
GregG
• ###### 9. Re: Cardinality linear decay in 9.2.0.8
Hemant K Chitale wrote:
Aah ! "The "formula" may vary between versions -- even patch levels." So, it was quite different in 9.2.0.8
Cost Based Oracle - Fundamentals, p.49: the linear decay was introduced between 10.1.0.2 and 10.1.0.4

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>
• ###### 10. Re: Cardinality linear decay in 9.2.0.8
GregG wrote:

So You mean the cardinality is constant regardless how far we are from max value stored in statistics ?
In this version of Oracle - yes; but It's easy enough to check with a few examples to see if the claim is realistic.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>
• ###### 11. Re: Cardinality linear decay in 9.2.0.8
You right.
``````insert into t select rownum, sysdate-(1+mod(rownum,10)) from dba_source where rownum <= 1000 ;

select count(*) from t where data = sysdate+100;
Card=100

select count(*) from t where data = sysdate-10;
Card=100

but why is that 10% of all rows ?``````
Regards
GregG

Edited by: GregG on Sep 25, 2012 1:13 PM
• ###### 12. Re: Cardinality linear decay in 9.2.0.8
GregG wrote:
You right.
``````insert into t select rownum, sysdate-(1+mod(rownum,10)) from dba_source where rownum <= 1000 ;

select count(*) from t where data = sysdate+100;
Card=100

select count(*) from t where data = sysdate-10;
Card=100``````
but why is that 10% of all rows ?
You've got 10 distinct values and no histograms, so any given value is assumed to return 1/10 of the rows.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>
• ###### 13. Re: Cardinality linear decay in 9.2.0.8
Thank You.
So ,after migration to 11.2 I'm expecting some problems :).
Regards
GregG