The following sections describe 11.9.2 changes to statistics, which include:
The sysstatistics table contains statistics related to the data in a column. Column level statistics describe the distribution of values in the column (and in the case of multicolumn density values, a set of columns). These are the “distribution” statistics formerly stored in the single distribution page of each index; they include the histogram and the density values. Also stored in sysstatistics is the date and time of the last modification of the column statistics.
Column statistics are now an attribute of a column, not an index. This is a fundamental change to the statistics. Because statistics are now an attribute of a column they can be placed on any column, whether it is part of an index or not.
The number of steps (cells) to use in the histogram can be specified in the create index or update statistics commands.
The systabstats table contains statistics related to objects: the table and index level statistics. Statistics related to tables and indexes are now stored in a single table and new statistics are also available. Table and index statistics are maintained dynamically by ASE. They should not be written directly; they will be quickly overwritten. Table and index statistics cannot be written with optdiag.
The statistics stored in systabstats are dynamic. An in-memory copy is kept and modified as changes occur. This in-memory copy is then flushed to systabstats in a number of ways, such as by housekeeper, update statistics, optdiag, checkpoint, shutdown, and sp_flushstats.
The update statistics command was given some new extensions and functionality. These include the ability to use update statistics to build or update statistics on an individual column, on all columns of an index or indexes or on all columns of a table. On DOL tables update statistics will use isolation level 0 (dirty reads) and avoid locking the table while running.
As in previous versions, running update statistics table_name [index_name] will update the statistics of the leading column (major attribute) of all indexes on the table or of the specified index. See the section Updating Statistics After Upgrade in Chapter 6of this manual for guidelines for running this command after upgrade.
See the Performance and Tuning Guide for information on histogram cells.
