db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-4115) Provide a way to drop statistics information
Date Wed, 09 May 2012 16:15:51 GMT

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

Mamta A. Satoor updated DERBY-4115:
-----------------------------------

    Attachment: DERBY4115_patch6_diff.txt

Attaching another patch, DERBY4115_patch6_diff.txt, which has more tests including upgrade
tests. Additionally, it also fixes code in AlterTableConstantAction to make sure that it sends
invalidation signal to dependent statements when statistics are dropped on a table. I found
this bug in my previous patch while trying to write a test where the query plan chosen for
the query should have changed after the statistics were dropped.

While writing the upgrade tests, I found that a meaningful test for drop statistics could
only be written for Derby releases 10.5 and higher. We have found that when constraints end
up sharing same backing index, Derby won't create statistics for them. This is issue DERBY-5702.
But if we run update statistics on that constraint, we will be able to get the statistics
for such a constraint. Later, when the constraint is dropped, because of DERBY-5681, the statistics
row for such a constraint(one that shares it's backing index with another constraint) is never
dropped. We can use drop statistics procedure introduced in this jira to take care of such
hanging indexes. But since update statistics procedure is only available in 10.5 and higher,
I couldn't demonstrate use of drop statistics to drop hanging statistics rows.

I have also taken care of some of the review comments by Kristian.

The details of all the changes in this patch are listed below. 
1)Added a new routine SYSCS_DROP_STATISTICS, with public access similar to SYSCS_UPDATE_STATISTICS.
This happens in DataDictionaryImpl, where SYSCS_DROP_STATISTICS is added to the list of public
access procedures in sysUtilProceduresWithPublicAccess 
2)The new stored procedure implementation is similar to update statistics, ie allow the routine
to go through ALTER TABLE where permission/privilege checking, table/schema/index name validations
happen automatically and we implement the routine logic through extension of ALTER TABLE syntax.
This new syntax for ALTER TABLE syntax(same as we did for update statistics) is an internal
syntax only and won't be available to an end user directly. 
3)This patch changes sqlgrammar.jj to recognize the following internal syntaxes for ALTER
TABLE 
a)ALTER TABLE tablename ALL DROP STATISTICS 
The existing(corresponding syntax) for update statistics is as follows 
ALTER TABLE tablename ALL UPDATE STATISTICS 
b)ALTER TABLE tablename STATISTICS DROP indexname 
The existing(corresponding syntax) for update statistics is as follows 
ALTER TABLE tablename UPDATE STATISTICS indexname 
Notice the two syntaxes for index level statistics are different for drop vs update.(the reason
for the syntax difference is explained above) 
4)After the statistics are dropped, we send invalidation signal to dependent statements so
they would get recompiled when they are executed next time. This will make sure that they
pick the correct plan given the statistics for the table.
5)The patch takes care of some of the test failures(expected failures because of the addition
of a new system procedure). 
6)The patch adds basic upgrade test for the new procedure. This test ensures that drop statistics
procedure is available only after hard upgrade. 
7)While writing the upgrade tests, I found that a meaningful test for drop statistics could
only be written for Derby releases 10.5 and higher. We have found that when constraints end
up sharing same backing index, Derby won't create statistics for them. This is issue DERBY-5702.
But if we run update statistics on that constraint, we will be able to get the statistics
for such a constraint. Later, when the constraint is dropped, because of DERBY-5681, the statistics
row for such a constraint(one that shares it's backing index with another constraint) is never
dropped. We can use drop statistics procedure introduced in this jira to take care of such
hanging indexes. But since update statistics procedure is only available in 10.5 and higher,
I couldn't demonstrate use of drop statistics to drop hanging statistics rows.

If there are no further comments on this patch, I will work on the committing this patch tomorrow.
Thanks
                
> Provide a way to drop statistics information
> --------------------------------------------
>
>                 Key: DERBY-4115
>                 URL: https://issues.apache.org/jira/browse/DERBY-4115
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.1.0
>            Reporter: Kathey Marsden
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY4115_patch1_diff.txt, DERBY4115_patch2_diff.txt, DERBY4115_patch3_diff.txt,
DERBY4115_patch4_diff.txt, DERBY4115_patch5_diff.txt, DERBY4115_patch6_diff.txt
>
>
> Now that DERBY-269 has been resolved,  users can update statistics, but once they do,
they are committed to using and maintaining the statistics, even if it doesn't improve performance
or they have difficulty maintaining the statistics on a regular basis.  It would be good to
have a way to drop statistics information so that users could revert to the prior behavior
if needed.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message