Histograms are kept on a per-column basis, rather than on a per-index basis. This has certain implications for managing statistics:
If a column appears in more than one index, update statistics, update index statistics, or create index updates the histogram for the column and the density statistics for all prefix subsets.
update all statistics updates histograms for all columns in a table.
Dropping an index does not drop the statistics for the index, since the optimizer can use column-level statistics to estimate costs, even when no index exists.
To remove the statistics after dropping an index, you must explicitly delete them using delete statistics.
If the statistics are useful to the query processor, and to keep the statistics without having an index, use update statistics, specifying the column name, for indexes where the distribution of key values changes over time.
Truncating a table does not delete the column-level statistics in sysstatistics. In many cases, tables are truncated and the same data is reloaded.
Since truncate table does not delete the column-level statistics, you need not run update statistics after the table is reloaded, if the data is the same.
If you reload the table with data that has a different distribution of key values, run update statistics.
You can drop and re-create indexes without affecting the index statistics, by specifying 0 for the number of steps in the with statistics clause to create index. This create index command does not affect the statistics in sysstatistics:
create index title_id_ix on titles(title_id)
with statistics using 0 values
This allows you to re-create an index without overwriting statistics that have been edited with optdiag.
If two users attempt to create an index on the same table, with the same columns, at the same time, one of the commands may fail due to an attempt to enter a duplicate key value in sysstatistics.
Executing update statistics on a column in a partition of a multipartition table updates the statistics for that partition, but also updates the global histogram for that column. This is done by merging the histograms for that column from each partition in a row-weighted fashion to arrive at a global histogram for the column.
Updating statistics on a multipartitioned table for a column, without specifying a partition, updates the statistics for each partition of the table for that column, and, as a last step, merges the partition histograms for the column to create a global histogram for the column.
The optimizer only uses the global histograms for a multipartitioned table during compilation, and does not read the partition histograms. This approach avoids the overhead of merging partition histograms at compilation time, and instead performs any merging work at DDL time.
