Adaptive Server optimizes join orders to minimize I/O. In most cases, the order that the query processor 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 query processor still chooses the best access method for each table. If you use forceplan and specify a join order, the query processor 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 query processor 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 both with and without forceplan.
If you use forceplan, your routine performance maintenance checks should include verifying that the queries and procedures that use forceplan 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.
You can disable hash joins at the session level. Also remember that an abstract plan allows full plan specification, including join order and join types.
See Chapter 12, “Creating and Using Abstract Plans,” and “Enabling and disabling merge joins”.
Forcing join order has these risks:
Misuse can lead to extremely expensive queries. Always test the query thoroughly with statistics io, and with and without forceplan.
It requires maintenance. You must regularly check queries and stored procedures that include forceplan. Also, each new version of Adaptive Server may eliminate the problems that lead you to incorporate index forcing, so check all queries that use forced query plans each time a new version is installed.
Before you use forceplan:
Check the showplan output to determine whether index keys are used as expected.
Use set option show normal to look for other optimization problems.
Run update statistics on the index.
Use update statistics to add statistics for search arguments on unindexed search clauses in the query, especially for search arguments that match minor keys in compound indexes.
Use set option show_missing_stats on to look for columns that may need statistics.
If the query joins more than four tables, use set table count, which may result in an improved join order.
See “Specifying the number of tables considered by the query processor”.
