Fragment-level statistics

For tables and indexes that have been partitioned according to fragment key values, the distribution statistics in the system catalog for some fragments might closely approximate current data distributions in those fragments, despite subsequent DELETE, INSERT, UPDATE, or MERGE operations that have caused the statistics for other fragments to become stale. For large tables that contain millions of rows, substantial resources of the database server can be conserved by updating only the subset of fragments with stale statistics, rather than recalculating distribution statistics for every fragment.

The STATLEVEL table attribute

For tables and indexes that are partitioned into multiple fragments by a distributed storage scheme, you can specify the granularity of its data distribution statistics, and you can specify the criteria by which stale statistics are defined. This can be accomplished by specifying keyword options of the Statistics Options clause in either of two DDL statements:

in the CREATE TABLE statement (when defining a new fragmented table)
in the ALTER TABLE statement (when changing the statistics granularity of an existing fragmented table).

In both cases, your options for specifying the granularity of the distribution statistics are the same:

STATLEVEL AUTO

Specifies that the database server apply the following criteria at runtime to determine if fragment-level distributions should be created:

The table is fragmented by EXPRESSION, by LIST, or by INTERVAL.
The table has more than 1,000,000 rows.

Unless both of these criteria are satisfied, table-level distributions are created. AUTO is the default setting in the CREATE TABLE statement, if you specify no explicit STATLEVEL setting.

STATLEVEL FRAGMENT

Data distributions will be created and maintained for each fragment. The FRAGMENT option is not valid for nonfragmented tables, or for tables that use a round robin storage distribution scheme.

STATLEVEL TABLE

All data distributions for the table will be created at the table level. This emulates the legacy behavior of Informix® servers earlier than version 11.70.

To support fragment level data distribution statistics, you must specify the name of an sbspace as the setting of the SYSSBSPACENAME configuration parameter, and you must also declare the name and allocate storage for that sbspace by using the -c -S option of the onspaces utility. For any table whose STATLEVEL attribute is set to FRAGMENT, the database server returns an error if SYSSBSPACENAME is not set, or if the sbspace to which is SYSSBSPACENAME is set is not properly allocated. For each fragment, the most recently calculated data distribution statistics are stored as a BLOB object in the sysfragdist.encdist column in the system catalog.

Data distribution statistics gathered at the fragment level can be aggregated to provide table level statistics from the constituent fragment statistics.

The STATCHANGE threshold for refreshing data distribution statistics

The same Statistics Options clause of the CREATE TABLE or ALTER TABLE statement can also specify a change threshold for data distribution statistics. The database server applies this STATCHANGE attribute of a fragmented table to all of the fragments of the table. The STATCHANGE table attribute can be set to an integer value, or you can specify the AUTO keyword:

integer: This defines an integer change threshold between 0 and 100 which defines how much table or fragment data is allowed to change before its statistics are considered stale in UPDATE STATISTICS operations that selectively update only stale distribution statistics.
AUTO: The threshold is the value of the STATCHANGE configuration parameter (or else 10, if no value is set for the STATCHANGE parameter). If the SET ENVIRONMENT statement has set a different value for the current session, that value overrides the default or explicit STATCHANGE configuration parameter setting.
AUTO is the default setting in the CREATE TABLE statement, if you specify no explicit STATCHANGE setting.

For the table and index fragments for which data distribution statistics are already stored in the system catalog, the STATCHANGE setting specifies the percentage of rows in the fragment that have been deleted, inserted, or modified by DML operations since its distribution statistics were most recently updated. (This is the same significance that STATCHANGE has for table-level statistics.)