IBM® Informix® 12.10
Optimizer directives
Optimizer directives are comments that tell the query optimizer how to execute a query. You can use optimizer directives to improve query performance.
- What optimizer directives are
Optimizer directives are specifications formatted as comments that provide information to the query optimizer about how to execute a query. - Reasons to use optimizer directives
In most cases, the optimizer chooses the fastest query plan. You can use optimizer directives when the optimizer does not choose the best query plan to perform a query, because of the complexity of the query, or because the query does not have enough information about the nature of the data. A poor query plan produces poor performance. - Preparation for using directives
In most cases, the optimizer chooses the fastest query plan. However, you can take steps to assist the optimizer and to prepare for using directives. - Guidelines for using directives
Guidelines for directives include frequently analyzing the effectiveness of the query and using negative directives. - Types of optimizer directives that are supported in SQL statements
Directives that are in SQL statements are embedded in queries. These directives include access-method directives, join-order directives, join-plan directives, and optimization-goal directives. - Configuration parameters and environment variables for optimizer directives
You can use the DIRECTIVES configuration parameter to turn on or off all directives that the database server encounters, and you can use the IFX_DIRECTIVES environment variable to override the setting of the DIRECTIVES configuration parameter. - Optimizer directives and SPL routines
Directives operate differently for a query in an SPL routine because a SELECT statement in an SPL routine is not necessarily optimized immediately before the database server executes it. - Avoiding index or prepared object exceptions by forced reoptimization
If the AUTO_REPREPARE configuration parameter and the IFX_AUTO_REPREPARE session environment variable are enabled, Informix automatically recompiles prepared statements and SPL routines after the schema of a referenced table is modified by a DDL statement. If the AUTO_REPREPARE configuration parameter or the IFX_AUTO_REPREPARE session environment variable is disabled, you can take steps to prevent errors. - External optimizer directives
If you are user informix, you can create, save, and delete external directives.
Parent topic: Performance Guide
To find the PDF, see Publications for the IBM Informix 12.10 family of products.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.