UPDATE STATISTICS

Column data distribution for columns that are index components

Column data distribution for columns that are not index components

Syntax

UPDATE ([ TABLE | INDEX
|[ ALL ] COLUMN ] STATISTICS [ AND ]) ... [ FOR table_name[ FOR FOR PARTITIONS partition_name_1, [partition_name_n, …]] [FOR TENANTS tenant1, [tenant2, …]] | FOR CDC SYSTEM TABLES] [WITH SAMPLE PERCENT n];

Notes

In the Update Statistics syntax, partition_name_1,…,partition_name_n are partition names of partitions belonging to table table_name.

FOR PARTITIONS clause can be used only with partitioned tables and it fetches statistics only from the specified partitions in the partition list.

FOR PARTITIONS clause, on updating index statistics, updates index statistics from only local indexes for the specified partitions in partition list and does not update global index statistics.

All statistics are obtained online. Obtaining statistics does not require an exclusive lock on the schema or any table locks. Rows written to statistics tables will be exclusively locked, as in every transaction performing updates. Therefore, statistics can be obtained while normal database operations continue.

Specifying TABLE STATISTICS obtains table cardinality only. Table cardinalities are stored in the SYSTABLSTAT system catalog table.

Specifying INDEX STATISTICS obtains statistics on the number of unique values in each index. Index statistics are stored in the SYSIDXSTAT system catalog table.

Specifying COLUMN STATISTICS (without ALL) obtains statistics on the data distribution of values for each column that is an index key component.

Specifying ALL COLUMN STATISTICS obtains statistics on the data distribution of values for all columns.

The STATISTICS phrase can be repeated so that up to three statistics can be requested by a single UPDATE STATISTICS statement.

By default, for the simple statement UPDATE STATISTICS, where the type of statistics is not specified, SQL will obtain table and index column statistics. This is equivalent to the statement UPDATE TABLE STATISTICS AND COLUMN STATISTICS.

A table containing LONG data types can get table, index, and/or column statistics. The columns that are LONG data types cannot get statistics.

Obtaining table statistics runs in time proportional to the table's primary index.

Obtaining column statistics runs in time proportional to the table's primary index, plus an additional amount proportional to the number of columns in the table.

The UPDATE STATISTICS command for data of a sample percentage is applicable only for column statistics. This is not applicable for index and table statistics.

Obtaining index statistics runs in time proportional to the total size for all indexes for the table.

Table statistics are often the most useful statistic, as they influence join order substantially.

Index statistics are important when a table has five or more indexes. This is especially true if some of the indexes are similar to one another.

Column statistics are the most useful when applications use range predicates, such as BETWEEN and the operators <, <=, > and >=.

The FOR TENANTS and FOR PARTITIONS clauses are mutually exclusive.

The FOR TENANTS clause can be used with multi-tenant tables to create statistics for only the specified tenants in the tenants list. When this caluse is not used, statistics are created for all tenants.

The tenant specified in the FOR TENANTS clause should not be a super-tenant.

The phrase FOR CDC SYSTEM TABLES is an alternative to the phrase FOR <tbl> and is the equivalent of running the UPDATE STATISTICS statement for all indivudual CDC system tables. The CDC system tables are as follows:

To get the best SQL query performance, or if a SQL performance problem occurs, be sure that the database has a full set of SQL statistics. To get a full set of SQL statistics, execute this SQL statement:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS;

At a slightly longer execution time, you can get even better SQL statistics by doing:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS;

Examples

The following example shows default commands for table cardinality and data distribution for index component columns:

The following example shows commands for table cardinality only:

The following example shows commands for new index statistics:

The following example shows commands for updating column statistics for index columns only:

The following example shows commands for updating statistics for all columns:

The following example shows commands for updating statistics for columns with a sample percentage:

UPDATE ALL COLUMN STATISTICS FOR Pub.Customer WITH SAMPLE PERCENT 5;

Note: Sample percentage must be a value between 0 to 100 and can be a decimal value. For example, 0.6.

The following example shows commands to obtain table cardinality and new index statistics and column statistics for all columns:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR Customer;

The following example updates table, and column statistics of partitions USA_Customer, EUROPE_Customer and ASIA_Customer for table Customer:

UPDATE STATISTICS FOR Customer FOR PARTITIONS USA_Customer, EUROPE_Customer, ASIA_Customer;

The following example updates column statistics of partitions USA_Customer, EUROPE_Customer and ASIA_Customer for table Customer:

UPDATE COLUMN STATISTICS FOR Customer FOR PARTITIONS USA_Customer, EUROPE_Customer, ASIA_Customer;

The following example updates all column statistics of partitions USA_Customer, EUROPE_Customer and ASIA_Customer for table Customer:

UPDATE ALL COLUMN STATISTICS FOR Customer FOR PARTITIONS USA_Customer, EUROPE_Customer, ASIA_Customer;

The following example shows the commands to get statistics by tenant:

UPDATE INDEX STATISTICS FOR table_name FOR TENANTS tenant1, tenant2;

The following example shows the commands to update statistics for all CDC system tables:

The following example shows the commands to update specific statistics for all CDC system tables:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS FOR CDC SYSTEM TABLES;