CREATE STATISTICS statement

Recreates the column statistics used by the optimizer, and stores them in the ISYSCOLSTAT system table.

Syntax

CREATE STATISTICS object-name [ ( column-list ) ]

object-name : 
table-name | materialized-view-name | temp-table-name

Remarks

The CREATE STATISTICS statement recreates the column statistics that the database server uses to optimize database queries, and can be performed on base tables, materialized views, local temporary tables, and global temporary tables. You cannot create statistics on proxy tables. Column statistics include histograms, which reflect the distribution of data in the database for the specified columns. By default, column statistics are automatically created for tables with five or more rows.

In rare circumstances, when your database queries are very variable, and when data distribution is not uniform or the data is changing frequently, you can improve performance by executing the CREATE STATISTICS statement on a table or column.

When executing, the CREATE STATISTICS statement updates existing column statistics regardless of the size of the table, unless the table is empty, in which case nothing is done. If column statistics exist for an empty table, they remain unchanged by the CREATE STATISTICS statement. To remove column statistics for an empty table, execute the DROP STATISTICS statement.

The process of executing CREATE STATISTICS performs a complete scan of the table. For this reason, careful consideration should be made before executing a CREATE STATISTICS statement.

If you drop statistics, it is recommended that you recreate them using the CREATE STATISTICS statement. Without statistics, the optimizer can generate inefficient data access plans, causing poor database performance.

The CREATE STATISTICS and DROP STATISTICS statements do not require or benefit from a commit with regards to the statistics, and only DROP STATISTICS has a commit as a side effect. However, the commit does not save changes to statistics (the statistics governor looks after that). Thus, while DROP STATISTICS does have a side effect of an automatic commit, it commits everything except for the dropping of the statistics.

Privileges

You must be the table owner, or have the MANAGE ANY STATISTICS or CREATE ANY OBJECT system privilege.

Side effects

Execution plans may change.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement updates the column statistics for the ProductID column of the SalesOrderItems table:

CREATE STATISTICS GROUPO.SalesOrderItems ( ProductID );