How SAPDBA Performs Update Statistics (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

How SAPDBA Performs Update Statistics

Use

SAPDBA for Informix performs update statistics on table columns using the following defaults:

Features

SAPDBA also performs update statistics at "table level" to update the size of indexes, number of rows and so on (this is done at level low).

For a table with multiple indexes where the indexes are similar, SAPDBA behaves as follows:

All leading index columns that are the same between two or more indexes are evaluated at level high.

The first index column that differs between two or more indexes is evaluated at level high.

Example

A table has two indexes as follows:

The result is that SAPDBA updates statistics at level high for columns A, B, C, E, and F and at level medium for columns G, H, I, and X.

SAPDBA can perform update statistics in parallel for greater throughput. For more information, see Updating Statistics from the Command Line with SAPDBA.

Activities

SAPDBA lets you update the statistics of a table when:

The difference between the statistics held by the optimizer for the number of rows in a table deviates from the current (that is, correct) value by more than a certain threshold. For more information about how to identify such tables, see Listing Tables with Deviation with SAPDBA.

No statistics have yet been calculated for a table. For more information about how to identify such tables (assuming they have at least one index), see Listing Tables with Non-Updated Indexes with SAPDBA.

A new index has been created on a table and the statistics have not been updated since. For more information about how to identify such tables, see Listing Tables with Non-Updated Indexes with SAPDBA.

An entry in the update statistics control table DBSTATC specifies update statistics for particular tables (such as large ones), overriding the default settings in SAPDBA. Refer to Customizing Update Statistics with SAPDBA.

See also:

Informix documentation