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 Tue, 01 May 2012 15:33:49 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_patch5_diff.txt

Attaching DERBY4115_patch5_diff, which is similar to earlier patch(DERBY4115_patch4_diff)
but now has the new procedure actually calling the ALTER TABLE to drop the statistics rather
than just ding a println. Additionally, I also had to change sqlgrammar.jj compared to the
last patch. This is because of the parsing conflict that can arise when a table has a column
named STATISTICS and an index named STATISTICS. If the user wants to drop the column STATISTICS,
they will issue ALTER TABLE t1 DROP STATISITCS. If the user wants to drop the statistics for
index STATISTICS, the patch 4(earlier patch) would generate ALTER TABLE DROP STATISITCS indexanme.
This causes the parser to get confused and give an error. In order to solve this problem,
without spending too much time on the syntax since it is an internal only syntax anyways(since
a user will not be issue this ALTER TABLE syntax directly to drop the statisitcs), I have
changed the grammar to use following internal syntax for dropping statisitcs ALTER TABLE STATISTICS
DROP indexname

So, following will be the internal syntax for various formats of update and drop statisitcs
generated through SYSCS_UPDATE_STATISTICS and SYSCS_DROP_STATISTICS
ALTER TABLE ALL DROP STATISITCS -- when the user wants all the statistics for a table to be
dropped
ALTER TABLE STATISTICS DROP indexname - when the user wants only indexname's statistics dropped
Corresponding update statistics syntax are as follows
ALTER TABLE ALL UODATE STATISITCS -- when the user wants all the statistics for a table to
be updated
ALTER TABLE UPDATE STATISTICS indexname - when the user wants only indexname's statistics
updated

For consistency purposes, I could change the update statistics single index to match the drop
statistics single index as follows but first I want to focus on finishing the changes for
drop statistics
ALTER TABLE STATISTICS UPDATE indexname - when the user wants only indexname's statistics
updated

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)The patch takes care of some of the test failures(expected failures because of the addition
of a new system procedure).
5)The patch adds basic upgrade test for the new procedure. This test ensures that drop statistics
procedure is available only after hard upgrade. 

Next step to do is to add more tests(regular and upgrade tests). Add more comments in the
code where appropriate.

Future improvement step (which could go as patch of it's own) could be -
For consistency purposes, we could change the update statistics single index to match the
drop statistics single index but first I want to focus on finishing the changes for drop statistics

                
> 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
>
>
> 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