Extending Informix Dynamic Server 2000 Improving UDR Performance

The query optimizer decides how to perform a query. A query plan is a specific way a query might be performed. A query plan includes how to access the table or tables included in the query, the order of joining tables, and the use of temporary tables. The query optimizer finds all feasible query plans. The optimizer estimates the cost to run each plan and then selects the plan with the lowest cost estimate

Tip: For more information on query optimization, refer to the "Performance Guide."

This section covers the following UDR-specific optimization topics:

• How SPL routines are optimized
• How updating statistics affects UDRs
• How to write optimization UDRs

During SPL optimization, the query optimizer evaluates the possible query plans and selects the query plan with the lowest cost. The database server puts the selected query plan for each SQL statement in an execution plan for the SPL routine. The database server optimizes each SQL statement within the SPL routine and includes the selected query plan in the execution plan.

The current optimization level set in an SPL routine affects how the SPL routine is optimized. The SQL statement, SET OPTIMIZATION, sets the optimization level, which in turn determines the algorithm that the query optimizer uses, as follows.

For SPL routines that remain unchanged or change only slightly, you might want to set the SET OPTIMIZATION statement to HIGH when you create the routine. This optimization level stores the best query plans for the routine. Then set optimization to LOW before you execute the routine. The routine then uses the optimal query plans and runs at the more cost-effective rate if reoptimization occurs.

When you create an SPL routine, the database server attempts to optimize the SQL statements within the routine at that time. If the tables cannot be examined at compile time (they might not exist or might not be available), the creation does not fail. In this case, the database server optimizes the SQL statements the first time that the SPL routine executes. The database server stores the optimized execution plan in the sysprocplan system catalog table for use by other processes.

The database server uses the 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 that an SPL routine executes after one of the following situations:

• Execution of any data definition language (DDL) statement (such as ALTER TABLE, DROP INDEX, 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.

The database server updates the sysprocplan system catalog table with the reoptimized execution plan.

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
• Numbver 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 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 Informix Guide to SQL: Reference. For more information about the UPDATE STATISTICS statement, refer to the Informix Guide to SQL: Syntax.