[jira] Created: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement update statistics)

[Apache Derby Developers mailing list]

Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
--------------------------------------------------------------------------------------------

         Key: DERBY-269
         URL: http://issues.apache.org/jira/browse/DERBY-269
     Project: Derby
        Type: New Feature
    Versions: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.0.0    
    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 fashion.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Apache Derby Developers mailing list]

     [ http://issues.apache.org/jira/browse/DERBY-269?page=comments#action_65955 ]
     
Kathey Marsden commented on DERBY-269:
--------------------------------------

Some sort of zero admin solution for updating statistics would be prefferable to the manual 'update statistics'

> Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
> --------------------------------------------------------------------------------------------
>
>          Key: DERBY-269
>          URL: http://issues.apache.org/jira/browse/DERBY-269
>      Project: Derby
>         Type: New Feature
>     Versions: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.0.0
>     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 fashion.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Apache Derby Developers mailing list]

     [ http://issues.apache.org/jira/browse/DERBY-269?page=all ]

Mike Matrigali updated DERBY-269:
---------------------------------

      Component: SQL
    Description:
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 fashion.

  was:
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 fashion.

    Environment:

> Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
> --------------------------------------------------------------------------------------------
>
>          Key: DERBY-269
>          URL: http://issues.apache.org/jira/browse/DERBY-269
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.0.2.2
>     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 fashion.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

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

Kristian Waagan updated DERBY-269:
----------------------------------

    Affects Version/s:     (was: 10.0.2.2)
                       10.3.0.0
                       10.2.2.0

Added 10.2.2.0 to the affected version list, as a user has reported severe performance degradation due to this issue. See http://www.nabble.com/Performance-Tuning-Problem-tf3549175.html for details. It is likely that trunk suffers from the same issue, so I'm adding that one as well.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.0.0
>            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 fashion.

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

    [ 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            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 fashion.

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

     [ 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            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 fashion.

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

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

Mamta A. Satoor reassigned DERBY-269:
-------------------------------------

    Assignee: Mamta A. Satoor

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Mamta A. Satoor commented on DERBY-269:
---------------------------------------

I will look at adding some sort of system procedure which can be invoked by the user manually to fix the statistics. Later on, we should look into providing a zero admin solution which should be able to share some of the code provided by the new system procedure.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Mamta A. Satoor commented on DERBY-269:
---------------------------------------

It appears that we still have core code left for "UPDATE STATISTICS" in Derby. It can be found in impl.sql.execute.UpdateStatisticsConstantAction When I work on writing a system procedure, hopefully I can base my code on what is in UpdateStatisticsConstantAction. Later, UpdateStatisticsConstantAction can then be removed.

Also, I think the new system procedure should go in SYSCS_UTIL schema where all th other utility procedures like SYSCS_COMPRESS_TABLE, SYSCS_INPLACE_COMPRESS_TABLE etc exist.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Mamta A. Satoor commented on DERBY-269:
---------------------------------------

************
A user could want to update the statistics for all the indexes on a given table or s/he might want to update statistics for just a specific index. Because of this, I think we should have 2 separate stored procedures as follows
1)Following could be used to update statistics for all the indexes on a given table
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS_ALL_INDEXES(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128))
2)Following could be used to update statistics of just one index
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS_ONE_INDEX(IN SCHEMANAME VARCHAR(128), IN INDEXNAME VARCHAR(128))
(I would love to hear other suggestions people may have for procedure names).
************


************
If 2 stored procedues look like an overkill then the other possible (concise but not so clear syntax) solution could be to just have one stored procedure for both the options as follows
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN INDEXNAME VARCHAR(128))

If user provides empty string or null for INDEXNAME, then statistics will be updated for all the indexes on the TABLENAME. But if a user specifies a specific INDEXNAME, then statistics will be updated only for the given INDEXNAME. (TABLENAME can be empty string or null when the user provides INDEXNAME. But if user has provided both INDEXNAME and TABLENAME, then that index should exist for the given table. If not, then an exception will be thrown).
************


If there are no preferences from the community, then I will go with the option of having 2 stored procedures. Feedback appreciated.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Knut Anders Hatlen commented on DERBY-269:
------------------------------------------

I would have preferred a single procedure with two parameters, schemaname and table_or_index_name. But since it's not possible to say whether the table or the index was meant if there's an index that has the same name as a table, I guess that's not an option. Having a single procedure allows us to have simpler procedure names, but I agree that it's not so clear with the three parameters.

As to the naming of the different procedures, could we pick one of them that the users are more likely to run and give a simpler name? For instance:
  SYSCS_UPDATE_STATISTICS - update one named index
  SYSCS_UPDATE_STATISTICS_ALL - update all indexes on a table

One more thing about procedure naming. I know that all the existing procedures in SYSCS_UTIL have names starting with SYSCS_ and cannot be changed. But the SYSCS_ prefix is redundant and makes the names unnecessarily long, so perhaps we could skip it for new procedures?

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Mamta A. Satoor commented on DERBY-269:
---------------------------------------

Knut, thanks for taking the time on this issue. I did further thinking about the implementation after my last comment. I think it will be good for us to use the existing code for ALTER TABLE which does basic schema/table verification, privilege checking etc. This code will be needed for the basic framework of update statistics. In order to make use of that code in ALTER TABLE, I am considering generating internal ALTER TABLE sql for update statistics. So, just like we generate internal alter table syntax for compress table, we will generate internal alter table sql for update statistics. This code of generate ALTER TABLE sql for update statistics will go in catalog.SystemProcedures class.

In order to generate ALTER TABLE sql, I need to know what table we are dealing with. Because of this, I would like to propose us having just one system procedure with following syntax
SYSCS_UTIL.UPDATE_STATISTICS(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN INDEXNAME VARCHAR(128))

When user wants to update the statistics of all the indexes, the 3rd parameter, INDEXNAME will be null or empty string. But when user wants to update a specific index, s/he will be required to provide all the three parameters, ie schema, table and index name. I think this keeps the stored procedure interface understandable because we are not making tablename optional sometimes anymore.

PS I agree that we can determine tablename from indexname if user just provided schema and indexname and then we can still generate ALTER TABLE sql but I think requiring the user to provide schemaname and tablename always and only making indexname optional will simplify the stored procedure interface.

Any feedback will be appreciated.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Knut Anders Hatlen commented on DERBY-269:
------------------------------------------

The latest proposal looks good to me. If we go for a single procedure, I agree that it's better to require the table name since it makes the interface easier to explain and understand.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Mike Matrigali updated DERBY-269:
---------------------------------


I like the proposal for a single procedure with 3 args as described, which can handle both the case of updating stats for all indexes and stats for a single named index.  my vote for name would be SYSCS_UPDATE_STATISTICS().

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

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

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

Mike Matrigali updated DERBY-269:
---------------------------------


I am ok with following the existing paradigm that other procedures use to implement this, ie. call the parser on new internal
syntax.  But I wonder if anyone knows how easy it might be to instead call directly whatever the parser generates in this case.
I don't know if it really matters, and maybe the current way is best with more code sharing.  I definitely thinks it makes sense
for the bulk of the code to go into alter table execution where it can share all the existing code for the same type of work.

> 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: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
>            Reporter: Stan Bradbury
>            Assignee: Mamta A. Satoor
>
> 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 fashion.

--
This message is au