This discussion is archived
13 Replies Latest reply: Sep 25, 2012 5:48 AM by 698658

# Cardinality linear decay in 9.2.0.8

Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
Also see

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

Hemant K Chitale
• ###### 3. Re: Cardinality linear decay in 9.2.0.8
Currently Being Moderated
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
Currently Being Moderated
Great , thanks .
Regards
GregG
• ###### 5. Re: Cardinality linear decay in 9.2.0.8
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
Maybe some more comments will show up.
GregG
• ###### 9. Re: Cardinality linear decay in 9.2.0.8
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
Thank You.
So ,after migration to 11.2 I'm expecting some problems :).
Regards
GregG

#### Legend

• Correct Answers - 10 points