Statistics options of the CREATE TABLE statement

Use the Statistics Options clause of the CREATE TABLE statement to set the values of the STATCHANGE property of a fragmented or nonfragmented table, and the STATLEVEL property of a fragmented table.

This syntax fragment is part of the CREATE TABLE statement.

Statistics Options


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

These table attributes control the threshold for automatic recalculation (STATCHANGE) and the granularity (STATLEVEL) of data distribution statistics.

The Statistics Options clause of the CREATE TABLE statement can define table statistics properties that allow the user to control the actions of

UPDATE STATISTICS, when that SQL statement is run without the FOR keyword,
and of UPDATE STATISTICS FOR TABLE, when that statement runs in LOW, MEDIUM, or HIGH mode.

The two table properties that the Statistics Options clause can set are STATCHANGE and STATLEVEL.

The STATCHANGE property

The STATCHANGE table attribute specifies the minimum percentage of changes (from UPDATE, DELETE, MERGE, 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 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 in the session environment variable as the default change threshold value.

The automatic mode for selectively updating table and fragment statistics can be enabled in any of the following ways:

The AUTO_STAT_MODE configuration parameter is set to 1 (or is not set). Enables automatic mode as the system default.
The AUTO_STAT_MODE session environment variable is set to ON. Enables automatic mode during the current session.
The UPDATE STATISTICS statement includes the AUTO keyword. Enables automatic mode while that UPDATE STATISTICS statement is running.

Important:

Enabling automatic mode has no effect, however, on any of these UPDATE STATISTICS statements:

UPDATE STATISTICS statements that end with the FORCE keyword.
UPDATE STATISTICS statements that include the FOR FUNCTION, FOR PROCEDURE, FOR ROUTINE, or FOR SPECIFIC keywords.

While automatic mode is enabled, UPDATE STATISTICS statements use the explicit or default STATCHANGE value to identify table, index, or fragment distributions statistics in the system catalog that are missing or stale, and selectively updates only the missing or stale statistics. For more information about the automatic mode for UPDATE STATISTICS operations, see the description of the AUTO_STAT_MODE configuration parameter in the HCL OneDB™ Administrator's Reference. See also AUTO_STAT_MODE session environment option and Using the FORCE and AUTO keywords.

The STATLEVEL property

The STATLEVEL property of a fragmented table can determine the level of granularity of its data distributions and index statistics. 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 all of the following conditions are true:
- The SYSSBSPACENAME configuration parameter setting specifies an existing sbspace.
- The table is fragmented by an EXPRESSION, INTERVAL, 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 statistics 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 specifies an existing sbspace.

If you use the Statistics Options clause to set the STATLEVEL property to FRAGMENT, the database server returns an error 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.