Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <169227863.1214498625182.JavaMail.jira@brutus>
Date: Thu, 26 Jun 2008 09:43:45 -0700 (PDT)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-269) Provide some way to update index
 cardinality statistics (e.g. reimplement update statistics)
In-Reply-To: <744936781.1115833984587.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12608492#action_12608492 ] 

Mamta A. Satoor commented on DERBY-269:
---------------------------------------

************
A user could want to update the statistics for all the indexes on a given table or s/he might want to update statistics for just a specific index. Because of this, I think we should have 2 separate stored procedures as follows
1)Following could be used to update statistics for all the indexes on a given table
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS_ALL_INDEXES(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128))
2)Following could be used to update statistics of just one index
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS_ONE_INDEX(IN SCHEMANAME VARCHAR(128), IN INDEXNAME VARCHAR(128))
(I would love to hear other suggestions people may have for procedure names).
************


************
If 2 stored procedues look like an overkill then the other possible (concise but not so clear syntax) solution could be to just have one stored procedure for both the options as follows
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN INDEXNAME VARCHAR(128))

If user provides empty string or null for INDEXNAME, then statistics will be updated for all the indexes on the TABLENAME. But if a user specifies a specific INDEXNAME, then statistics will be updated only for the given INDEXNAME. (TABLENAME can be empty string or null when the user provides INDEXNAME. But if user has provided both INDEXNAME and TABLENAME, then that index should exist for the given table. If not, then an exception will be thrown). 
************


If there are no preferences from the community, then I will go with the option of having 2 stored procedures. Feedback 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.