Adaptive Server lets you specify these optimization choices by including commands in a query batch or in the text of the query:
The order of tables in a join
The number of tables evaluated at one time during join optimization
The index used for a table access
The I/O size
The cache strategy
The degree of parallelism
In a few cases, the optimizer fails to choose the best plan. In some of these cases, the plan it chooses is only slightly more expensive than the “best” plan, so you need to weigh the cost of maintaining forced options against the slower performance of a less than optimal plan.
The commands to specify join order, index, I/O size, or cache strategy, coupled with the query-reporting commands like statistics io and showplan, can help you determine why the optimizer makes its choices.
WARNING! Use the options described in this chapter with caution. The forced query plans may be inappropriate in some situations and may cause very poor performance. If you include these options in your applications, check query plans, I/O statistics, and other performance data regularly.
These options are generally intended for use as tools for tuning and experimentation, not as long-term solutions to optimization problems.
