Updates all statistics information for a given table. You can run update all statistics on a single data partition.
update all statistics table_name [partition data_partition_name]
is the name of the table for which statistics are being updated.
is the name of the partition to be updated. Statistics for each local index partition on the data partition is updated. Does not update statistics for global indexes.
Updates all statistics for the salesdetail table:
update all statistics salesdetail
Updates all statistics for the smallsales partition on salesdetail table:
update all statistics salesdetail partition smallsales
update all statistics updates all the statistics information for a given table. Adaptive Server keeps statistics about the distribution of pages within a table, and uses these statistics when considering whether or not to use a parallel scan in query processing on partitioned tables, and which index (es) to use in query processing. The optimization of your queries depends on the accuracy of the stored statistics.
Histogram statistics are created on each column, either through an index scan of a leading column or a projection of the column into a work table, followed by a sort.
Density statistics are created for all the prefix subsets of the columns of index (es) whose statistics is being updated. For example, if an index is on columns c1, c2 and c3, then the prefix subsets are (c1,c2) and (c1, c2, c3).
When you run update all statistics on a single data partition, histograms are generated for each leading column of the local indexes using an index scan. For all other columns, including leading columns of a global index, update all statistics performs a data scan followed by a sort.
update statistics commands create partition-specific statistics. Global statistics are implicitly created during partition statistics creation. The partition statistics serve as input to global statistics creation and enable per-partition DDL operations. Global statistics are used by the optimizer.
update all statistics regenerates and update the table statistics stored in systabstats for each data and index partition of the table. If the update all statistics command is run for a specific data partition, the table statistitics are generated and updated only for that data partition and any local index partitions. Global indexes are skipped.
ANSI SQL – Compliance level: Transact-SQL extension.
update all statistics permission defaults to the table owner and is not transferrable.
Commands update statistics, update index statistics, update statistics, update table statistics
