db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
Date Wed, 11 Jun 2008 19:38:45 GMT

     [ https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Mike Matrigali updated DERBY-269:

I have not seen any other suggestions, how about the following zero admin solution?  It is
not perfect - suggestions welcome.

Along with the statistics storing, save how many rows were in the table when exact statistics
were calculated.  This number is
0 if none have been calculated because index creation happened on an empty table.  At query
compile time when we look
up statistics we automatically recalculate the statistics at certain threshholds - say something
like row count growing past next threshhold : 10, 100, 1000,
100000 - with upper limit being somewhere around how many rows we can process in some small
amount of time - like 1 second on a modern laptop.  If we are worried about response time,
maybe we background queue the stat gathering rather than waiting with maybe
some quick load if no stat has ever been gathered.  The background gathering could be optimized
to not interfere with locks by
using read uncommitted.  

I think it would be useful to also have the manual call just to make it easy to support customers
and debug issues in the field.  There is proably always some dynamic data distribution change
that in some case won't be picked up by the automatic algorithm.
Also just very useful for those who have complete control of the create ddl, load data, run
stats, deliver application process.

> Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
> --------------------------------------------------------------------------------------------
>                 Key: DERBY-269
>                 URL: https://issues.apache.org/jira/browse/DERBY-269
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions:,,,,
>            Reporter: Stan Bradbury
> Performance problems are being reported that can be resolved by updating the cardinality
statistics used by the optimizer.  Currently the only time the statistics are guaranteed to
be an up-to-date is when the index is first created on a fully populated table.  This is most
easily accomplished on an existing table by using the command: 
>    alter table <table-name> compress [sequential]  
> Compress table is an I/O intensive task.  A better way to achieve this would be to re-enable
parser support for the 'update statistics' command or re-implement the update in some other

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message