[
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
