Adaptive Server optimizes join orders to minimize I/O. In most cases, the order that the optimizer chooses does not match the order of the from clauses in your select command. To force Adaptive Server to access tables in the order they are listed, use:
set forceplan [on|off]
The optimizer still chooses the best access method for each table. If you use forceplan, specifying a join order, the optimizer may use different indexes on tables than it would with a different table order, or it may not be able to use existing indexes.
You might use this command as a debugging aid if other query analysis tools lead you to suspect that the optimizer is not choosing the best join order. Always verify that the order you are forcing reduces I/O and logical reads by using set statistics io on and comparing I/O with and without forceplan.
If you use forceplan, your routine performance maintenance checks should include verifying that the queries and procedures that use it still require the option to improve performance.
You can include forceplan in the text of stored procedures.
set forceplan forces only join order, and not join type. There is no command for specifying the join type; you can disable merge joins at the server or session level.
See “Enabling and disabling merge joins” for more information.
