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] Commented: (DERBY-269) Provide some way to update index cardinality statistics (e.g. reimplement update statistics)
Date Fri, 27 Jun 2008 14:24:45 GMT

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


Mime
View raw message