The scope of UPDATE STATISTICS statements

The specifications that follow the STATISTICS keyword determine the scope of UPDATE STATISTICS statements.

• If the UPDATE STATISTICS statement includes no FOR keyword, then the scope cannot be explicitly restricted to a single table or to a single SPL routine.

  In the case of no syntax tokens following the STATISTICS keyword, the default scope is to update statistics in LOW mode for every permanent table in the current database, including its system catalog tables, and to update the statistics in the systables, syscolumns, and sysindices system catalog tables.

  Both of the following examples produce the same result:

  UPDATE STATISTICS;
  UPDATE STATISTICS LOW;

  For database tables with distributed storage, the CREATE TABLE or ALTER TABLE statement that defined the Statistics Options properties of the table determines the table-level or fragment-level granularity of these distribution statistics.

  If you include only the MEDIUM or HIGH keyword, the scope is the same, although the resources required for the operation are greater.

• If you include only the FOR TABLE keywords without also specifying the name or synonym of a table, the database server recalculates distributions on all of the tables in the current database, and on all of the temporary tables in your session. UPDATE STATISTICS has no effect, however, on objects defined by the CREATE EXTERNAL TABLE statement.
• If you specify a table after the FOR TABLE keywords without also specifying a list of columns, the database server recalculates the statistical distributions on all of the columns of the specified table.
• If you specify a table after the FOR TABLE keywords and a list of columns, the database server recalculates the statistical distributions of only the specified columns.

In summary, for UPDATE STATISTICS statements that include no database object names, the database server updates distribution statistics on the following table objects by default:

• If you specify no scope or mode,

  UPDATE STATISTICS;

  distribution statistics are calculated in LOW mode for all permanent tables in the database.
• If you specify the scope as FOR TABLE, but with no list of tables,

  UPDATE STATISTICS MEDIUM FOR TABLE;

  distribution statistics are calculated in the specified mode for all permanent tables in the database and for all temporary tables in the session. In the example above, MEDIUM specifies a more costly statistics mode than the default LOW mode, but has no effect on which table objects are in scope.

For UPDATE STATISTICS statements that include the FOR FUNCTION, FOR PROCEDURE, or FOR ROUTINE keywords, the scope depends on whether you also include the name of a routine and an argument list.

• If you specify the scope as FOR PROCEDURE, or FOR ROUTINE, or FOR FUNCTION, but with no list of SPL routines, as in these examples,

  UPDATE STATISTICS FOR FUNCTION;
  UPDATE STATISTICS FOR PROCEDURE;
  UPDATE STATISTICS FOR ROUTINE;

  the database server takes these actions:
  • reoptimizes the execution plans for the DML statements in every SPL routine in the database,
  • and updates the sysprocplan system catalog table with the reoptimized execution plan.
  It does not, however, refresh any table distribution statistics.

An UPDATE STATISTICS statement that specifies the identifier of an overloaded SPL routine identifier, where more than one routine have the same name, but different parameters, can reoptimize more than one SPL routine.

• If you specify the scope with the name of an SPL routine, but with no list of parameters,

  UPDATE STATISTICS FOR FUNCTION someSPLroutine;
  UPDATE STATISTICS FOR PROCEDURE someSPLroutine;
  UPDATE STATISTICS FOR ROUTINE someSPLroutine;

  the execution plans are reoptimized for the DML statements in any SPL routine with the specified identifier in the database. If the routine name is overloaded, because more than one SPL routine is registered in the database with that name, every SPL routine with that name is reoptimized.
• If you specify the scope with the name of an overloaded SPL routine and a parameter list,

  UPDATE STATISTICS FOR FUNCTION someSPLroutine(INT, CHAR(140));
  UPDATE STATISTICS FOR PROCEDURE someSPLroutine(INT, CHAR(140));
  UPDATE STATISTICS FOR ROUTINE someSPLroutine(INT, CHAR(140));

  the execution plans are reoptimized for the DML statements in any SPL routine with the specified identifier and the same argument types in the database.

If the name of an SPL routine is not overloaded, however, you can omit the parameter list and use the SPECIFIC keyword to restrict the scope of the UPDATE STATISTICS statement to the specified SPL routine:

UPDATE STATISTICS FOR SPECIFIC FUNCTION someSPLroutine;
UPDATE STATISTICS FOR SPECIFIC PROCEDURE someSPLroutine;
UPDATE STATISTICS FOR SPECIFIC ROUTINE someSPLroutine;

For example, if the AUTO_STAT_MODE setting is ON for the session, and one or more tables that the Perform_work function processed have a high STATCHANGE threshold, you might decide to override the selective refreshing of table statistics by including the FORCE keyword:

UPDATE STATISTICS FOR SPECIFIC FUNCTION Perform_work FORCE;

This overrides the restriction to tables with stale statistics, so that the execution plan for Perform_work will be based on the current data values, whether or not the column distribution statistics in the system catalog satisfy the STATCHANGE criteria for stale data,