Updating 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 reoptimizes SQL statements in all SPL routines and changes the statistics for all tables.

• UPDATE STATISTICS FOR TABLE

  When you specify the FOR TABLE clause without a table name, the database server changes the statistics for all tables and does not reoptimize any SQL statements in 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 IBM Informix: Guide to SQL Reference. For more information about the UPDATE STATISTICS statement, refer to the IBM Informix: Guide to SQL Syntax.