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 setting 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, the 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