[
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
