Statistics options of the ALTER TABLE statement

Use the Statistics Options clause of the ALTER TABLE statement to change values of the STATCHANGE property of a fragmented or nonfragmented table, and the STATLEVEL property of a fragmented table. These table properties control the threshold for recalculation in automatic mode, and the granularity of data distribution statistics.

This clause supports the same syntax options as the Statistics Options clause of the CREATE TABLE statement. This syntax fragment is part of the ALTER TABLE statement.


Statistics Options

|--+----------------------------------+------------------------->
   |             .-AUTO-------------. |   
   '-STATCHANGE--+-change_threshold-+-'   

>--+-------------------------+----------------------------------|
   '-STATLEVEL--+-FRAGMENT-+-'   
                +-TABLE----+     
                '-AUTO-----'

Element	Description	Restrictions	Syntax
`change_ threshold`	Percentage of changed data rows that defines stale distribution statistics	Must be an integer in the range 0 - 100	Literal Number

Usage

The Statistics Options clause of the ALTER TABLE statement can define or modify table statistics properties that can affect these operations while the automatic mode of UPDATE STATISTICS is enabled:

UPDATE STATISTICS statements without the FOR keyword
UPDATE STATISTICS FOR TABLE statements in LOW, MEDIUM, or HIGH mode.

The ALTER TABLE statement can modify the specified or default values of the STATCHANGE and STATLEVEL properties that were set when the table was created, or that were set by a previous ALTER TABLE statement.

The STATCHANGE property

The STATCHANGE table property specifies the minimum percentage of changes (from UPDATE, DELETE, LOAD, and INSERT operations) on the rows in the table or fragment since the previous calculation of distribution statistics) to consider the statistics stale. You can specify the percentage change as either an integer value in the range 0 - 100, or you can use the AUTO keyword to apply the current STATCHANGE configuration parameter setting in the ONCONFIG file, or the setting in the session environment, as the default change threshold value.

The AUTO keyword of the UPDATE STATISTICS statement also enables comparing the proportion of rows with changed values to the STATCHANGE setting to determine whether the statistics in the system catalog are stale. Including the AUTO keyword in the UPDATE STATISTICS statement enables checking for stale statistics (and selectively updating only the tables or fragments with stale or missing statistics) during the current UPDATE STATISTICS operation.

When the AUTO_STAT_MODE configuration parameter or the AUTO_STAT_MODE session environment variable enables the automatic mode, the UPDATE STATISTICS statement uses the explicit or default STATCHANGE value to identify table, index, or fragment distributions whose statistics are missing or stale, and updates only the missing or stale statistics. For more information about the automatic mode for UPDATE STATISTICS operations, see information about the AUTO_STAT_MODE configuration parameter in the HCL Informix® Administrator's Reference.

The STATCHANGE property and the automatic mode of UPDATE STATISTICS do not directly affect the optimization of SPL execution plans, or UPDATE STATISTICS statements that include the FORCE, FOR FUNCTION, FOR PROCEDURE, FOR ROUTINE, or FOR SPECIFIC keywords.

The STATLEVEL property

The STATLEVEL property can determine the level of granularity of the data distributions and index statistics of fragmented tables. It can take one of the following three values, with AUTO being the default, if no value is specified at creation time:

TABLE specifies that all distributions for the table be created at the table level.
FRAGMENT specifies that distributions be created and maintained for each fragment.
AUTO specifies that the database server apply criteria at run time to determine whether fragment-level distributions are necessary. These criteria require that the following conditions are true:
- The SYSSBSPACENAME configuration parameter setting specifies an existing sbspace
- The table is fragmented by an EXPRESSION, INTERVAL, Rolling Window, or LIST strategy
- The table has more than a million rows.
If any of these criteria are not satisfied, the database server creates table-level distributions, rather than fragment-level.

These properties are always applied. If the STATLEVEL setting is AUTO, this setting overrides the default values.

Note: The SYSSBSPACENAME configuration parameter, which must be set when the database server instance is initialized, specifies the sbspace in which the database server stores fragment-level data distribution statistics. These are stored as BLOB objects in the encdist column of the syfragsdist system catalog table. For the database server to support fragment level statistics, the SYSSBSPACENAME configuration parameter setting must specify an existing sbspace.

If you use the Statistics Options clause to set the STATLEVEL property to FRAGMENT, the database server returns an error -9814 ("Invalid default sbspace name") if either of the following is true:

The SYSSBSPACENAME configuration parameter is not set
The sbspace that SYSSBSPACENAME specifies was not properly allocated by the onspaces -c -S command.

Example of changing the STATLEVEL

Suppose that table tabFrag uses a fragmented distribution strategy other than ROUND ROBIN, and includes a BLOB or CLOB column called smartblob. You decide to keep the storage distribution strategy, but to use TABLE, rather than FRAGMENT, as the STATLEVEL granularity.

The following SQL statements implement those changes:

ALTER TABLE tabFrag STATLEVEL TABLE;

UPDATE STATISTICS LOW 
   FOR TABLE tabFrag (smartblob) DROP DISTRIBUTIONS;

UPDATE STATISTICS HIGH 
   FOR TABLE tabFrag (smartblob);

The statements above have these respective effects:

Change the STATLEVEL to TABLE, by using the Statistics Options clause of ALTER TABLE.
Discard the current fragment-level distributions of tabFrag.smartblob in the sysfragdist system catalog table, by using UPDATE STATISTICS LOW.
Create new table-level statistics for tabFrag in the sysdistrib system catalog table, by using UPDATE STATISTICS HIGH.

In the last statement above, the default HIGH resolution of 0.5 implies that the tabFrag.smartblob distribution statistics are based on approximately 200 bins.

Example of resetting the STATCHANGE value

For the same tabFrag table whose STATLEVEL property was changed from FRAGMENT to TABLE in the previous section, suppose that you also decide to change its STATCHANGE value to AUTO from whatever setting it currently has, and to replace the HIGH mode distribution statistics with a MEDIUM mode.

The following SQL statements implement those changes:

ALTER TABLE tabFrag STATCHANGE AUTO;

UPDATE STATISTICS LOW 
   FOR TABLE tabFrag (smartblob) DROP DISTRIBUTIONS;

UPDATE STATISTICS MEDIUM 
   FOR TABLE tabFrag (smartblob) AUTO;

In the last s

These are the respective effects on the tabFrag table of the statements above:

The Statistics Options clause of ALTER TABLE changes the STATCHANGE setting to AUTO.
The UPDATE STATISTICS LOW statement discards the current fragment-level distributions of tabFrag.smartblob from the sysfragdist system catalog table, but recalculates no table-level statistics. (Here the LOW keyword is required syntax to enable the DROP DISTRIBUTIONS operation.)
The UPDATE STATISTICS MEDIUM statement refresh the table-level statistics for tabFrag in the sysdistrib system catalog table in automatic mode, by including the AUTO keyword.

tatement above, the default MEDIUM resolution of 2.5 implies that the tabFrag.smartblob distribution statistics are based on approximately 40 bins.

In this example, the AUTO setting for STATCHANGE in the ALTER TABLE statement and the AUTO keyword in the UPDATE STATISTICS MEDIUM apply automatic mode to the recalculation of tabFrag.smartblob statistics. Although no tabFrag.smartblob data values changed since the UPDATE STATISTICS HIGH operation in the STATLEVEL example, the preceding UPDATE STATISTICS LOW statement dropped the HIGH mode statistics, so the statistics for 100% of the rows became "missing" during the DROP DISTRIBUTIONS operation. In this case, no selective recalculation of tabFrag.smartblob statistics occurs, despite the automatic mode.

The examples above illustrate some effects of ALTER TABLE options for redefining the statistics properties of a table, and the effects of those modified properties on some UPDATE STATISTICS operations in automatic mode. They do not necessarily illustrate a recommended or efficient sequence of decisions for redefining the granularity or the resolution of column statistics for tables and for table fragments in the system catalog.