You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Stan Bradbury (JIRA)" <de...@db.apache.org> on 2005/05/11 19:53:04 UTC
[jira] Created: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement 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
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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_ver1.txt
DERBY269_update_statistics_svndiff_ver1.txt
I have a patch for implementing update statistics as a stored procedure. The main logic is to convert the stored procedure request to an ALTER TABLE... sql(bear in mind that this generated sql is not available for the user to issue directly. This alter table syntax will be accepted only when sql is not coming directly from the user. This is similar in concept to what we do for compress table) and go through the existing ALTER TABLE code to do table/schema/index validation, ensuring proper permissions exist for user to call update statistics etc. The actual code to update the statistics has been (literally) copied from the existing disabled code for update statistics in Derby (impl.sql.execute.UpdateStatisticsConstantAction.java) The user can invoke update statistics for just one index on a table or for all indexes on a table. The syntax of the new stored procedure is as follows
call syscs_util.SYSCS_UPDATE_STATISTICS(schemaname, tablename, indexname)
If the user specifies null for the indexname, then all the indexes on the tablename will have their statistics updated. If the user does specify an indexname, then statistics will be updated only for that index.
The patch here include engine changes and upgrade changes. It also fixes existing tests that were impacted by the engine changes. TODO : I need to add more tests for the actual functionality. The existing disabled test selectivity.sql appears to have some tests for old disabled (non-compliant) update statistics syntax. I will use these existing tests as the basis for the new junit tests for the new stored procedure.
The files impacted by this patch are as follows. I will briefly go over the actual code changes in the following paragraph
$ 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
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
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
M java\testing\org\apache\derbyTesting\functionTests\master\db2Compatibility.out
Parser, compile and execute time changes for the new stored procedure For the stored procedure, we generate one of the following 2 ALTER TABLE ... sqls This sql is generated if user wants us to update the statistics of all the indexes on a table
ALTER TABLE (schemaname.)tablename ALL UPDATE STATISTICS
or
This sql is generated if user wants us to update the statistics of only one index on a table
ALTER TABLE (schemaname.)tablename UPDATE STATISTICS indexname
1)sqlgrammar.jj
We will first make sure that we are at data dictionary version 10.5 or higher in order to accept the new ALTER TABLE sql syntax. Next, we will make sure that this ALTER TABLE was generated through internal code and not from the user code. One these 2 criterias are made, we will generated the compile time node for ALTER TABLE sql.
2)AlterTableNode.java
The changes in this class are simple and simply make sure that we are ready to accept the new ALTER TABLE... syntax
3)GenericConstantActionFactory.java
Since we have more parameters to pass from compile phase to execute phase for ALTER TABLE now, I needed to make changes into GenericConstantActionFactory.java from those additional params.
4)AlterTableConstantAction.java
The changes in this file are copied from the existing disabled code for update statistics in impl.sql.execute.UpdateStatisticsConstantAction.java. This existing code relies on a method in IndexRowGenerator.java which I added back again (this method was removed by Dan a long time back because it was not being used by any active code in Derby. The method was removed as part of revision 565966).
5)SystemProcedures.java
This is where we intercept the call to SYSCS_UPDATE_STATISTICS and convert it into internal syntax of ALTER TABLE... sql
The following changes in engine code are related to upgrade code
1)DataDictionaryImpl.java
It marks the database to be at version 10.5 and it adds the new system stored procedure SYSCS_UPDATE_STATISTICS to the data dictionary for 10.5 release.
2)DD_Version.java
This provides a way to add the stored procedure SYSCS_UPDATE_STATISTICS to the data dictionary during hard upgrade to 10.5 release of an existing db.
Following existing tests needed some modifications
1)RolesTest.java, GrantRevokeDDLTest.java, db2Compatibility.out
The number of stored procedures have increased by one and hence these tests have to take that into account.
Following tests changes are for upgrade testing(added a new test for 10.5 release and the new test is called Changes10_5.java
1)upgradeTests/_Suite.java, Changes10_2.java, UpgradeRun.java
I will appreciate any feedback on the patch.
> 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Assigned: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Resolved: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613665#action_12613665 ]
Mamta A. Satoor commented on DERBY-269:
---------------------------------------
Knut, thanks for reviewing the patch. Answers to your questions/comments
***********************
- since the code is copied from UpdateStatisticsConstantAction, I assume USCA will be removed from the repository in a later patch?
Yes, I am planning on removing this file when I commit the changes.
***********************
***********************
- UpdateStatisticsConstantAction uses a strange mix of tabs and spaces for indentation. It would be great if the indentation is cleaned up when the code is moved.
I took care of indentation in my codeline (sorry, it is not in the patch) in the transferred code to AlterTableConstantAction
***********************
***********************
* - SystemProcedures.SYSCS_UPDATE_STATISTICS() uses tabs/spaces inconsistently
I took care of indentation in my codeline (sorry, it is not in the patch).
**********************
**********************
- comment in upgradeTests/_Suite.java has the wrong version number:
+ {10, 4, 1, 3}, // 10.3.1.4 (April 24, 2008 / SVN 648739)
Thanks for catching this. I fixed the version number in my codeline.
**********************
**********************
- SYSCS_UPDATE_STATISTICS is added to Changes10_2. Shouldn't that file only test procedures that were added in Derby 10.2?
What I found is if I do not add SYSCS_UPDATE_STATISTICS to the list of stored procedures in Changes10_2, I get error that there are 6 rows in SYSROUTINEPERMS but we are looking only for 5 rows. My understanding of upgrade test is not upto date but it sounds like we are testing the upgrade from 10.2 release to 10.5. If that is right, then I think I do need to add SYSCS_UPDATE_STATISTICS in Changes10_2.
**********************
> 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12608814#action_12608814 ]
Mamta A. Satoor commented on DERBY-269:
---------------------------------------
I am little torn about what the name should be for this new system procedure. I see Knut's point about prefix SYSCS_ being redundant in the name but I think in order to be consistent with the naming convention for all the existing stored procedures, it may be better to continuee the tradition of starting the procedure name with SYSCS_. I find myself leaning more towards the name SYSCS_UTIL.SYSCS_UPDATE_STATISTICS but I can be pursuaded if the community thinks otherwise.
> 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Matt Doran (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613458#action_12613458 ]
Mamta A. Satoor commented on DERBY-269:
---------------------------------------
I spent time looking through selectivity.sql and tried converting the test cases into just a sql file where we do not rely on the old functionality which is not supported anymore. The existing selectivity.sql depends on the features like Object.methods eg runtimestatistics().rts.getEstimatedRowCount(), runtimestatistics().getScanStatisticsText('TEMPLATE') inside of sql.
eg
create view showstats as
select cast (conglomeratename as varchar(20)) indexname,
cast (statistics.toString() as varchar(40)) stats,
creationtimestamp createtime,
colcount ncols
from sys.sysstatistics, sys.sysconglomerates
where conglomerateid = referenceid;
Such object lookup is not available in Derby. I will spend more time on it but it may not work out with the curent Derby functionality.
I was wondering if community had any ideas/test cases which I can use for the new stored procedure. I can do basic testing which shows that the stored procedure runs etc but it will be nice to see the real impact of running the stored procedure on a query before and after the update statistics runs. Thanks for any help with 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_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.
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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 automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613587#action_12613587 ]
Knut Anders Hatlen commented on DERBY-269:
------------------------------------------
I've only had a brief look at the patch, so I haven't studied the details, but the general approach looks fine to me. Some questions/comments:
- since the code is copied from UpdateStatisticsConstantAction, I assume USCA will be removed from the repository in a later patch?
- UpdateStatisticsConstantAction uses a strange mix of tabs and spaces for indentation. It would be great if the indentation is cleaned up when the code is moved.
- SystemProcedures.SYSCS_UPDATE_STATISTICS() uses tabs/spaces inconsistently
- comment in upgradeTests/_Suite.java has the wrong version number:
+ {10, 4, 1, 3}, // 10.3.1.4 (April 24, 2008 / SVN 648739)
- SYSCS_UPDATE_STATISTICS is added to Changes10_2. Shouldn't that file only test procedures that were added in Derby 10.2?
> 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.
[jira] Commented: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Updated: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-269) Provide some way to update index
cardinality statistics (e.g. reimplement update statistics)
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.