Adaptive Server version 11.9.2 increases the flexibility of the statistics used by the query optimizer. Optimizer statistics are now kept in two system tables, systabstats and sysstatistics. A new utility, optdiag, can extract statistics and allows editing of statistics. optdiag makes many of these statistics viewable for the first time.
Statistics are now kept on a per-column basis, rather than a per-index basis. Query costing in the optimizer has been enhanced to use column-level statistics for search arguments and joins, even if indexes do not exist on the column.
The update statistics command now supports storing statistics for unindexed columns. New update index statistics syntax facilitates creating statistics on all columns used in an index, and update all statistics now generates statistics for all columns in a table. A new command, delete statistics, can be used to drop column-level statistics, since drop index no longer removes statistics.
New statistics are kept to support the new locking schemes. Additional statistics track the clustering of data rows and data pages in physical storage and improve the cost estimates made by the optimizer. Some queries on tables using new locking schemes are optimized differently than queries on tables using the old locking scheme.
