Automatic reoptimization

In some situations, the database server reoptimizes an SQL statement the next time an SPL routine.

If the AUTO_REPREPARE configuration parameter or the IFX_AUTO_REPREPARE session environment variable is disabled, the following error can result when prepared objects or SPL routines are executed after the schema of a table referenced by the prepared object or indirectly referenced by the SPL routine has been modified:

-710  Table <table-name> has been dropped, altered, or renamed.

The database server uses a dependency list to keep track of changes that would cause reoptimization the next time that an SPL routine executes.

The database server reoptimizes an SQL statement the next time an SPL routine executes after one of the following situations:
  • Execution of any data definition language (DDL) statement (such as ALTER TABLE, DROP INDEX, and CREATE INDEX) that might alter the query plan
  • Alteration of a table that is linked to another table with a referential constraint (in either direction)
  • Execution of UPDATE STATISTICS FOR TABLE for any table involved in the query

    The UPDATE STATISTICS FOR TABLE statement changes the version number of the specified table in systables.

  • Renaming a column, database, or index with the RENAME statement

Whenever the SPL routine is reoptimized, the database server updates the sysprocplan system catalog table with the reoptimized execution plan.