update index statistics

Updates the statistics for all columns in an index.

Syntax

update index statistics
	table_name [[partition data_partition_name] |
	[ [index_name [partition index_partition_name]]]
	[using step values]
	[with consumers = consumers] [, sampling=N [percent]]
		[, no_hashing | partial_hashing | hashing]
	 	[, max_resource_granularity = N percent]]
	 	[, histogram_tuning_factor = int]
		[, print_progress = int]

Parameters

Examples

Usage

  • update index statistics, when used with a table name and an index name, updates statistics for all columns in the specified index. If update index statistics is used with just a table name, it updates statistics for all columns in all indexes of the table.

  • If you run update index statistics against large tables, the command fails with error number 1105 if tempdb is not large enough to process the command.

  • Specifying the name of an unindexed column or the nonleading column of an index generates statistics for that column without creating an index.

  • Histogram statistics are created for each column of indexes whose statistics is being updated.

  • Density statistics are created for all the prefix subsets of the columns of index (es) whose statistics are being updated.

  • If you use update index statistics on a specific partition, you update global statistics implicitly as well.

  • The partition statistics serve as input to global statistics creation and enable per-partition DDL operations. Global statistics are used by the optimizer.

  • update index statistics also regenerates and updates the table statistics stored in systabstats for each data and index partition of the table the command updates. If you run the update index statistics command for a specific data partition, the table statistitics are generated and updated only for that data partition and for any local index partitions. Global indexes are skipped. If you run the update index statistics for a specific index partition, only the table statistics for that index partition are updated.

  • The with consumers clause is designed for use on partitioned tables on RAID devices, which appear to the SAP ASE server as a single I/O device, but which are capable of producing the high throughput required for parallel sorting.

  • The update index statistics command generates a series of update statistics operations that use the same locking, scanning, and sorting as the equivalent index-level and column-level command. For example, if the salesdetail table has a nonclustered index named sales_det_ix on salesdetail (stor_id, ord_num, title_id), the update index statistics salesdetail command performs these update statistics operations:
    update statistics salesdetail sales_det_ix 
    update statistics salesdetail (ord_num)
    update statistics salesdetail (title_id)
When using hash-based statistics:
  • The partial_hashing parameter uses hashing on columns only when it produces histograms that are as accurate and of equivalent quality as those produced by sorting (low-domain cases), otherwise, the partial_hashing parameter uses sorting for high-domain cases.

  • Hashing in high-domain cases may produce histograms with up to a 50 percent fewer steps compared to sorting.

  • Although hash-based statistics do not require the tempdb disk space or procedure cache memory used by sorting, it may use a significant amount of tempdb buffer cache memory.

  • Large sorts used for the no_hashing parameter may scavenge statements in the statement cache and stored procedures to release procedure cache memory to support the sort.

  • max_resource_granularity limits the amount of tempdb buffer cache memory used for hashing or partial_hashing. It does not affect the amount of memory used by the no_hashing parameter or sorting.

  • If you include the partial_hashing parameter, and a previous histogram on a column exists that indicates a high-domain column, then the SAP ASE server assumes that sort-based statistics are required on this column. If no previous histogram exists on a column, then the SAP ASE server assumes this column is low-domain until the high-domain limit is reached.

See also Parallel Sorting in the Performance and Tuning Guide.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be the table owner or a user with update statistics permission on the table to run update index statistics.

Related reference
delete statistics
update all statistics
update statistics
update table statistics