Update statistics for an SPL routine

The database server stores statistics about the amount and nature of the data in a table in the systables, syscolumns, and sysindices system catalog tables. The statistics that the database server stores include the following information:
  • Number of rows
  • Maximum and minimum values of columns
  • Number of unique values
  • Indexes that exist on a table, including the columns and functional values that are part of the index key

The query optimizer uses these statistics to determine the cost of each possible query plan. Run UPDATE STATISTICS to update these values whenever you have made a large number of changes to the table.

The UPDATE STATISTICS statement can have no modifying clauses or several modifying clauses, as in the following statements:
UPDATE STATISTICS FOR TABLE tablename;
UPDATE STATISTICS FOR ROUTINE routinename;
Execution of UPDATE STATISTICS affects optimization and changes the system catalog in the following ways:
  • No UPDATE STATISTICS statement

    If you do not execute UPDATE STATISTICS after the size or content of a table changes, no SQL statements within the SPL routine are reoptimized. The next time a routine executes, the database server reoptimizes its execution plan if any objects that are referenced in the routine have changed.

  • UPDATE STATISTICS;

    When you specify no additional clauses, the database server updates statistics in LOW mode for all tables. (But it does not reoptimize SQL statements in any SPL routines.)

  • UPDATE STATISTICS FOR TABLE;

    When you specify the FOR TABLE clause without a table name, the database server refreshes the statistics for all tables. (But it does not reoptimize SQL statements in any SPL routines,)

  • UPDATE STATISTICS FOR TABLE table name;

    When you specify a table name in the FOR TABLE clause, the database server changes the statistics for the specified table. The database server does not reoptimize any SQL statements in SPL routines.

  • UPDATE STATISTICS...
    When you specify one of the following clauses, the database server reoptimizes SQL statements in all SPL routines. The database server does not update the statistics in the system catalog tables.
    • FOR FUNCTION
    • FOR PROCEDURE
    • FOR ROUTINE
  • UPDATE STATISTICS... routine name
    When you include a routine name in one of the following clauses, the database server reoptimizes SQL statements in the named routine. The database server does not update the statistics in the system catalog tables.
    • FOR FUNCTION routine name
    • FOR PROCEDURE routine name
    • FOR ROUTINE routine name

After the database server reoptimizes SQL statements, it updates the sysprocplan system catalog table with the reoptimized execution plan. For more information about sysprocplan, refer to the HCL OneDB™ Guide to SQL: Reference. For more information about the UPDATE STATISTICS statement, refer to the HCL OneDB Guide to SQL: Syntax.