Using the FORCE and AUTO keywords

In sessions or in databases where the automatic UPDATE STATISTICS mode is enabled, you can optionally use either the FORCE keyword or the AUTO keyword to control the scope of the UPDATE STATISTICS statement when it updates the current distribution statistics of tables and columns in the system catalog. These keywords affect only table and fragment statistics, and are not valid in operations on SPL routine statistics.

If you omit both the FORCE keyword and the AUTO keyword, the effect of the UPDATE STATISTICS statement on table and fragment distribution statistics is determined by the explicit or default setting of the AUTO_STAT_MODE configuration parameter, unless the AUTO_STAT_MODE session environment variable is set to override that configuration parameter for the current session.

Specifying either of the FORCE or AUTO keywords affects only the current UPDATE STATISTICS operation. The database server issues an exception if you attempt to include both the FORCE and the AUTO keywords in the same UPDATE STATISTICS statement.

The FORCE keyword

The FORCE keyword refreshes the statistics for all tables and columns within the specified scope. If automatic mode for the UPDATE STATISTICS statement is enabled, the FORCE keyword overrides automatic mode, so that values of the STATCHANGE attributes of tables and fragments within the scope of the FOR TABLE specification are ignored, as if the AUTO_STAT_MODE setting were OFF for the current UPDATE STATISTICS FORCE operation.

The two examples that follow apply the FORCE keyword, respectively, to the default table scope of UPDATE STATISTICS, and to a nonfragmented individual table called tableN. 

UPDATE STATISTICS FORCE;
UPDATE STATISTICS MEDIUM FOR TABLE tableN FORCE;
The first statement instructs the database server to take the following actions:
  • Recalculate the distribution statistics in LOW mode for every permanent table and table fragment in the database, ignoring whether statistics for each table are stale according to their STATCHANGE thresholds.
  • Store the new distributions in the system catalog tables.
The second statement instructs the database server to take the following actions:
  • Recalculate the column distribution statistics in MEDIUM mode for table tableN.
  • Update all the system catalog tables that store distribution statistics for that table.

Including the FORCE keyword emulates the previous UPDATE STATISTICS behavior of HCL OneDB™ database servers before version 11.70.

The AUTO keyword

The AUTO keyword causes the database server to run the UPDATE STATISTICS statement in automatic mode, but only for tables and fragments whose statistics are missing or stale. The distribution statistics are not refreshed for any tables or fragments whose STATCHANGE value is below the specified threshold.

The following statements specify the AUTO keyword.

UPDATE STATISTICS AUTO;
UPDATE STATISTICS MEDIUM FOR TABLE tableN AUTO;
The first statement instructs the database server to take the following actions:
  • Examine every permanent table in the database, and recalculate only the missing or stale data distribution statistics for tables and fragments in which the percentage of new, deleted, or changed rows since the table distributions were last calculated exceeds the STATCHANGE threshold for that table. This might be an empty set, if no tables or table fragments exceed their STATCHANGE thresholds.
  • If any tables or fragments qualified to be recalculated, store their new statistics in the appropriate system catalog tables.
The second statement instructs the database server to take the following actions: .
  • If the current distribution statistics for tableN do not exceed the STATCHANGE threshold for that table, take no action.
  • If the percentage of new, deleted, or changed rows indicate that the current distributions are stale, recalculate the column distribution statistics for table tableN in MEDIUM mode, and update all the system catalog tables that store distribution statistics for that table.

When sufficiently accurate statistics are already available to the query optimizer for some tables or table fragments, the AUTO option avoids unnecessary recalculations. In that case, an UPDATE STATISTICS AUTO operation requires less time, without detriment to query performance, than a corresponding UPDATE STATISTICS FORCE operation.