The abstract plan syntax, used by Adaptive Server Enterprise to force the query plan chosen by the optimizer, has been extended to allow several query-level settings, that were previously available only at the session level. The optimization criteria are handled at the session level by the following set statements:
set nl_join|merge_join|hash_join|... 0|1
The use ... abstract plan syntax has been extended to accept any number of use forms before the abstract plan derived table. Previously, 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, with Adaptive Server 15.0.1, you can include several statements in the same abstract plan in two ways:
By using several use statements, for example:
select ... plan “(use optgoal allrows_dss) (use nl_join off) (...)”
By 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.
Abstract plan optimization, which supersedes optimization goal defaults criteria, can be set after you set the optimization goal.
