You can use abstract plans to force the query plan the query processor choses to allow several query-level settings. See Chapter 7, “Controlling Optimization,” for more information about using abstract plans at the query level.
The optimization criteria are handled at the session level by the following set statements:
set nl_join|merge_join|hash_join|... on | off
The use ... abstract plan syntax accepts any number of use forms before the abstract plan derived table. In versions of Adaptive Server earlir than 15.0, optgoal and opttimeout could not be in the same abstract plan with a derived table. For example, this statement would need to be separate from a optgoal statement in a query:
select ... plan "(use opttimeoutlimit 10) (i_scan r)"
However, you can include several statements in the same abstract plan by:
Using several use statements. For example:
select ... plan "(use optgoal allrows_dss) (use nl_join off) (...)"
Placing several items within one use form. For example:
select ... plan "(use (optgoal allrows_dss) (nl_join off)) (...)"
At the query level, use the optimization goal (opt_goal) or timeout (opttimeout) setting with the use ... abstract plan syntax. At the session level, use these settings with the set plan ... syntax:
Optimization goal.
Optimization timeout
For example, join r outer to s and enable the hash_join without an optimization goal (opt_goal):
select ... > > plan > "(use hash_join on) > > (join (scan r) (scan s))"
This example uses the opt_goal and allrows_oltp statements, but with hash_join enabled:
select ... > > plan > > "(use opt_goal allrows_oltp)(use hash_join on)"
When setting the optimization goal and the optimization criteria at the query level, the order of the use statements does not affect the outcome.
The abstract plan optimization goal is set first, and sets the optimization goal defaults for the optimization criteria.
You can set abstract plan optimization, which supersedes optimization goal defaults criteria, you set the optimization goal.
