As you analyze SET EXPLAIN output to find out how the database server executes a query or transaction, you might find that the optimizer does not choose the plan that you consider most efficient. Optimizer directives can force the optimizer to choose specific access methods and join plans.
The optimizer analyzes the data distribution statistics and known information about the table fragmentation. It does not guess which query plan is efficient overall; it carefully calculates the costs of several plans and chooses the best one. If you specify a directive to correct some aspect of the query plan, you might limit the optimizer choices for other aspects of the query plan.
Use optimizer directives for a query only when other tuning efforts fail. Before you try an optimizer directive, consider these steps:
After checking the sqlexplain.out, if the optimizer still does not choose the plan you prefer, consider the optimizer directives described in the following sections. Before you include optimizer directives in queries on your production system, however, make sure that the directives actually improve the query efficiency. Continue to evaluate SET EXPLAIN output to ensure that the directive has the effect that you intend, particularly if the distribution of data changes.
For the syntax of using optimizer directives, refer to the IBM Informix: Guide to SQL Syntax. For information about how specific access and join plans work, see Parallel Query Execution. For detailed information about using optimizer directives, refer to the IBM Informix: Guide to SQL Syntax.
The following table lists all available optimizer directives.
| Directive Name | Description |
|---|---|
| Directives that
affect joins |
|
| ORDERED | Join tables in the order in which the query code lists them. For an example of the effect of this directive in queries that reference views, see Using Join-Order Directives with Views. |
| USE_NL | Join each specified table as the inner table to the remaining table using a nested-loop join. |
| AVOID_NL | Avoid using any of the specified tables as the inner table in a nested-loop join. |
| USE_HASH | Use a hash join for the listed tables. You can also specify whether the table is used to build the hash table or to probe the hash table. If the build or probe option is specified, you can also specify whether to broadcast to all the hash table to all coservers. |
| AVOID_HASH | Avoid using each of the specified tables either as the build or probe side of the hash join or in any hash join at all. You can specify whether to avoid tables in the build or probe side of a hash join. If you do not specify either option, listed tables are not used in either part of a hash join. |
| Directives that affect
the access method |
|
| INDEX | Use the specified index to access the table. If the directive lists more than one index, the optimizer chooses the index that yields the least cost. Forcing an access method might change the join method that the optimizer chooses. |
| INDEX_ALL | Use all specified indexes on the specified table. |
| AVOID_INDEX | Avoid plans that make use of one or more of the specified indexes to scan the specified table. |
| FULL | Perform a full table scan. |
| AVOID_FULL | Avoid full table scan for the specified table. |
| Directives that specify
how the query plan should be rewritten |
|
| NESTED | Suppress the unnesting of subqueries. |
| Miscellaneous
directives |
|
| EXPLAIN | Use this directive to write the query plan to the default sqexplain.out file. Do not use this directive if explicit SET EXPLAIN output has already been specified for the query. The EXPLAIN directive is useful for tuning and debugging queries. |