[DERBY-269] Provide some way to update index cardinality statistics (e.g. reimplement update statistics)

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
• Fix Version/s: 10.5.1.1
• Component/s: SQL
• Assignee: Mamta A. Satoor
• Reporter: Sun Seng David Tan
• Created: 12/May/05 02:51
• Updated: 21/Jan/11 17:48
• Resolved: 21/Jul/08 18:07

Description

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.

Issue Links

• is related to: DERBY-3787 Document the new system stored procedure SYSCS_UTIL.SYSCS_UPDATE_STATISTICS which was added as part of DERBY-269
• relates to: DERBY-4116 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS should update the store estimated row count for the table
• relates to: DERBY-4115 Provide a way to drop statistics information