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