The Adaptive Server cost-based optimizer uses statistics about the tables, indexes, partitions, and columns named in a query to estimate query costs. It chooses the access method that the optimizer determines has the least cost. But this cost estimate cannot be accurate if statistics are not accurate.
Some statistics, such as the number of pages or rows in a table, are updated during query processing. Other statistics, such as the histograms on columns, are updated only when you execute update statistics, or when indexes are created.
If your query is performing slowly and you seek help from Technical Support or a Sybase newsgroup on the Internet, one of the first questions you are likely be asked is “Did you run update statistics?” Use the optdiag command to see when update statistics was last run for each column on which statistics exist:
Last update of column statistics: Aug 31 2004 4:14:17:180PM
Another command you may need for statistics maintenance is delete statistics. Dropping an index does not drop the statistics for that index. If the distribution of keys in the columns changes after the index is dropped, but the statistics are still used for some queries, the outdated statistics can affect query plans.
Histogram statistics from a global index are more accurate than histogram statistics generated by a local index. For a local index, statistics are created on each partition, and are then merged to create a global histogram using approximations as to how overlapping histogram cells from each partition should be combined. With a global index, the merge step, with merging estimates, does not occur. In most cases, there is no issue with update statistics on a local index. However, if there are significant estimation errors in queries involving partitioned tables, histogram accuracy can be improved by creating and dropping a global index on a column rather than updating the statistics on a local index.
