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
Under some circumstances, the query processor does not choose the best plan. Occasionally, the plan choosen by the query processor is only slightly more expensive than the “best” plan, so you must 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 query processor makes its choices.
WARNING! Use the options described in this chapter with caution. Forced query plans may be inappropriate in some situations and may cause poor performance. If you include these options in your applications, regularly check query plans, I/O statistics, and other performance data.
These options are generally intended for use as tools for tuning and experimentation, not as long-term solutions to optimization problems.
