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

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

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

Thanks for the answers, Mamta. I'm not up to date on the upgrade tests either, but I think you're right, Changes10_2 seems to be testing upgrade from Derby 10.2 (or later) to the current release.

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svnstat_ver1.txt
>
>
> 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 updated DERBY-269:
----------------------------------

    Attachment: DERBY269_update_statistics_svnstat_ver2.txt
                DERBY269_update_statistics_svndiff_ver2.txt

I have final patch DERBY269_update_statistics_svndiff_ver2.txt attached to this jira. The output of svn stat -q is as follows. The patch is not very different from the first patch. The changes in this patch are that
1)I have added a new test lang\UpdateStatisticsTest.java which does basic testing like
  a)attempt to update statistics of a table that does not exist
  b)attempt to update statistics of a non-existent index on a pre-existing table
  c)create table, create index, load data in table, make sure there is no statistics created for index because there was no data in the table when it was created, create another index, it should get statistics created because there is data in the table, now perform update statistics on first index and it should also get statistics created for it
  d)issue internally generated ALTER TABLE ... sql for system procedure directly but that will fail because such a sql can only be generated internally and is  not available to the users
  e)try to issue update statistics on global temporary table and that should fail
2)I have added a new test in existing GrantRevokeDDLTest.java to show that the new system procedure has public access available on it. The only requirement is the table on which it is being issued should be accessible to the user issuing it.
3)In my first patch, I had forgotten to add the keyword STATISTICS into non-reserved non-sql92 list and because of that, db2Compatibility test was failing incorrectly and I had provided a fix for the test. But marking the keyword correctly in sqlgrammar.jj makes sure that no changes are required in db2Compatibility test.
4)In addition, I have reformatted the following files to use correct space/tab format when compared to the first patch.
5)I have removed UpdateStatisticsConstantAction,java since the code inside it has now been moved to AlterTableConstantAction.
6)I fixed the incorrect version number that I had in upgradeTests/_Suite.java

Will appreciate any comments on the patch. If no comments, then work on committing it in a day or two. Thanks

svn stat -q
M      java\engine\org\apache\derby\impl\sql\compile\AlterTableNode.java
M      java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
D      java\engine\org\apache\derby\impl\sql\execute\UpdateStatisticsConstantAction.java
M      java\engine\org\apache\derby\impl\sql\execute\GenericConstantActionFactory.java
M      java\engine\org\apache\derby\impl\sql\execute\AlterTableConstantAction.java
M      java\engine\org\apache\derby\impl\sql\catalog\DataDictionaryImpl.java
M      java\engine\org\apache\derby\impl\sql\catalog\DD_Version.java
M      java\engine\org\apache\derby\iapi\sql\dictionary\IndexRowGenerator.java
M      java\engine\org\apache\derby\iapi\sql\dictionary\DataDictionary.java
M      java\engine\org\apache\derby\catalog\SystemProcedures.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\RolesTest.java
A      java\testing\org\apache\derbyTesting\functionTests\tests\lang\UpdateStatisticsTest.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\_Suite.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\GrantRevokeDDLTest.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\_Suite.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\Changes10_2.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\UpgradeRun.java
A      java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\Changes10_5.java

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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 updated DERBY-269:
----------------------------------

    Derby Info: [Patch Available]

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12614455#action_12614455 ]

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

I think the changes look good. A couple of comments (none of them are
important enough to hold a commit):

 - You may want to make the fields that were added to AlterTableNode
   private. And the comment describing those fields will probably be
   clearer if it is split so that it explains each variable in a
   separate comment (javadoc, preferably) instead of all in the same
   comment.

 - The patch adds some trailing blanks which it would be good to clean
   out.

 - In AlterTableConstantAction.updateStatistics() I think it would be
   good to move the assignment of heapCC up before the
   try/finally. Then we don't need to check heapCC!=null in the
   finally clause. The same goes for the assignment of gsc in the same
   method.

 - I think IndexRowGenerator.getNullIndexRow() will be easier to
   understand if the variable i is declared in the for loop to reduce
   it's scope, and the first statement after the for loop is changed
   from using (i+1) to (baseColumnPositions.length+1).

 - In SystemProcedures.SYSCS_UPDATE_STATISTICS(), the two if
   statements could be merged into a single one (the body of the first
   one could be moved into the else branch of the second one).

 - The SQL in the javadoc comment of SYSCS_UPDATE_STATISTICS() lacks
   the update keyword.

 - Just wondering, but would it be better to move the optional ALL
   keyword in the syntax, so that the syntax became "UPDATE ALL
   STATISTICS" instead of "ALL UPDATE STATISTICS"? That feels like a
   more natural order to me. Not that important as long as it's just
   internal SQL, though.

 - UpdateStatisticsTest has the wrong class name in the header.

 - UpdateStatisticsTest (five instances): try/catch should call fail()
   at the end of the try block to ensure that an exception was
   thrown. Could use helper method
   BaseJDBCTestCase.assertStatementError() for this.

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12614661#action_12614661 ]

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

If I have understood correctly, unique indexes always have up to date cardinality statistics because cardinality == row count. If that's the case, one possible optimization is to skip the unique indexes when SYSCS_UPDATE_STATISTICS is called.

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12614785#action_12614785 ]

Mike Matrigali commented on DERBY-269:
--------------------------------------

is the cardinality of a unique index 1 or is it row count?

It is also more complicated than just skipping unique indexes, it depends on the number of columns in the index because
in a multi-column index, multiple cardinalities are calculated.  So for instance on an index on columns A,B,C there are
actually 3 cardinalities calculated:
A
A,B
A,B,C

I agree that the calculation of cardinality of A,B,C could/should be short circuited for a unique index.  

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12614841#action_12614841 ]

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

Knut, thanks for your thorough review of the last patch. I have taken care of all the comments (except the one about changing the sql grammar to "UPDATE ALL STATISTICS" instead of "ALL UPDATE STATISTICS". I hope since this is an internal sql only, it is ok to leave it as it is. Please let me know if you think otherwise.

I have committed the changes (which also addresses Knut's comment) in trunk using revision 677998

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12614899#action_12614899 ]

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

Thanks Mamta! I'm fine with the changes and with keeping the internal syntax from the previous patches.

Mike,
It looks to me as if the cardinality is the number of unique values, so I think the cardinality of a unique index is equal to its row count (for the full key, that is).
You're right that we can't short circuit it if we have a multi-column index. I don't know if it's worth the extra complexity to short circuit the A,B,C case, since we'd have to scan the entire index anyway. For a single-column unique index it sounds like a good idea, though.

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12615293#action_12615293 ]

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

I just realized that I left some code comments in AlterTableConstantAction which should have been removed when I committed the code last week. Will go ahead and remove those comments shortly.

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12615296#action_12615296 ]

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

Cleaned up the code comments with revision 678460.

> 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
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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 resolved DERBY-269.
-----------------------------------

       Resolution: Fixed
    Fix Version/s: 10.5.0.0
       Derby Info:   (was: [Patch Available])

I am closing this jira entry since it now provides a manual way to update the statistics. I will create a new jira entry for the documentation counterpart of the code changes.

In addition, I will also enter a new jira entry for providing some automatic way to update the statistics when they are not upto date.

> 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
>             Fix For: 10.5.0.0
>
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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=12615361#action_12615361 ]

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

Added jira entry DERBY-3788 Provide a zero-admin way of updating the statisitcs of an index

Another related jira entry DERBY-3790 Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column.

> 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
>             Fix For: 10.5.0.0
>
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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 agree now that the selectivity maintains the "number of unique values"  and then uses this along with the "number of rows" to export a selectivity percentage.

As to the short circuit discussion, I just don't know if the optimizer will actually ever ask for the selectivity of something
that it knows is unique - i actually hope not.  Note that the actual row count is only available with a full scan, the one
maintained by store is only an estimate.  But in this case it may just be good enough to store the estimate row count as
both the row count and the number of rows.

> 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
>             Fix For: 10.5.0.0
>
>         Attachments: DERBY269_update_statistics_svndiff_ver1.txt, DERBY269_update_statistics_svndiff_ver2.txt, DERBY269_update_statistics_svnstat_ver1.txt, DERBY269_update_statistics_svnstat_ver2.txt
>
>
> 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.