db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Matt Doran (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
Date Tue, 04 Dec 2007 04:23:43 GMT

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

Matt Doran commented on DERBY-269:

I agree that it would be great to have a way to update stats without a full compress/rebuild
(which is pretty IO intensive).  We user a derby in a commercial application, and we found
some extremely poor performance if the stats were not up-to-date.    Updating the stats made
the problem query run in less than 1 second (it previously took 22 minutes!)

See here for the details: http://thread.gmane.org/gmane.comp.apache.db.derby.user/8098   and
here for the resolution: http://thread.gmane.org/gmane.comp.apache.db.derby.user/8100/focus=8103

It would be great if derby could update the statistics itself.  It would probably result in
a much better out-of-the-box performance for most users.  For now we've implemented a maintenance
task in our application that periodically performs the compress operation. 

Maybe an interim step to make the documentation very clear that you *must* run the compress
operation once your database is populated with representative data.

> 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