db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Selecting a few rows is really slow when ordering by primary key
Date Wed, 01 Feb 2012 13:51:59 GMT
Hi Simon,

Glad to hear that regenerating the statistics fixed your problem. More 
inline...

On 2/1/12 4:07 AM, Simon Chatelain wrote:
> ...
>
> But one small question remains, do you think that it is enough to run 
> SYSCS_UPDATE_STATIS
> TICS only once, or should I plan to run this procedure at regular 
> interval (let's say once a week or once a month) in case the 
> statistics are not correctly updated as new rows are inserted in the 
> database ?
>
If you upgrade to 10.8, the statistics should be regenerated 
automatically. The statistics are supposed to be recalculated when the 
number of rows in the table is too far out of sync with the number of 
rows estimated by the statistics. "Too far out of sync" means that the 
ratio between the two numbers is more than e. If statistics are not 
being generated often enough, you can adjust the triggering ratio by 
setting the following Derby property:

derby.storage.indexStats.debug.lndiffThreshold

That property defaults to be 1.0. By setting it to a value between 0.0 
and 1.0, you should speed up the rate at which Derby recalculates 
statistics. By setting the property to a larger value, you should slow 
down the rate at which Derby recalculates statistics. Let us know if you 
think that statistics are not being regenerated often enough. This is a 
new feature which we are eager to tune and improve.

Hope this helps,
-Rick

Mime
View raw message