Update statistics when they are not generated automatically

The UPDATE STATISTICS statement updates the statistics in the system catalog tables that the optimizer uses to determine the lowest-cost query plan.

Important: You do not need to run UPDATE STATISTICS operations when the statistics are generated automatically.

The following statistics are generated automatically by the CREATE INDEX statement, with or without the ONLINE keyword:

Index-level statistics, equivalent to the statistics gathered in the UPDATE STATISTICS operation in LOW mode, for B-tree indexes.
Column-distribution statistics, equivalent to the distribution generated in the UPDATE STATISTICS operation in HIGH mode, for a non-opaque leading indexed column of an ordinary B-tree index.

To ensure that the optimizer selects a query plan that best reflects the current state of your tables, run UPDATE STATISTICS at regular intervals when the statistics are not generated automatically.

Tip: If you run UPDATE STATISTICS LOW on the sysutils database before you use ON-Bar, the time ON-BAR needs for processing is reduced.

The following table summarizes when to run different UPDATE STATISTICS statements if the statistics are not generated automatically. If you need to run UPDATE STATISTICS statements and you have many tables, you can write a script to generate these UPDATE STATISTICS statements.


When to Execute	UPDATE STATISTICS Statement	Reference for Details and Examples
Number of rows has changed significantly	UPDATE STATISTICS LOW DROP DISTRIBUTIONS	Update the statistics for the number of rows or Drop data distributions if necessary when upgrading
For all columns that are not the leading column of any index	UPDATE STATISTICS LOW	Creating data distributions
Queries have non-indexed join columns or filter columns	UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY	Creating data distributions
Queries have an indexed join columns or filter columns	UPDATE STATISTICS HIGH table (leading column in index)	Creating data distributions
Queries have a multicolumn indexed defined on join columns or filter columns	UPDATE STATISTICS HIGH table (first differing column in multicolumn index)	Creating data distributions
Queries have a multicolumn indexed defined on join columns or filter columns	UPDATE STATISTICS LOW table (all columns in multicolumn index)	Creating data distributions
Queries have many small tables (fit into one extent)	UPDATE STATISTICS HIGH on small tables	Creating data distributions
Queries use SPL routines	UPDATE STATISTICS FOR PROCEDURE	Reoptimizing SPL routines

For information about the specific statistics that the database server keeps in the system catalog tables, see Statistics held for the table and index.