You can set specific optimization criteria for each session. The optimization criteria represent specific algorithms or relational techniques that may or may not be considered when Adaptive Server creates a query plan. By setting individual optimization criteria on or off, you can fine-tune the query plan for the current session.
Each optimization goal has default settings for each
optimization criterion. Resetting optimization criteria may interfere
with the default settings of the current optimization goal and produce
an error message—although Adaptive Server will honor the
new setting.
Sybase recommends that you set individual optimization criteria only rarely and
with caution if it is necessary to fine-tune a particular
query. Overriding optimization goal settings in this way can overly
complicate query administration. Always set optimization criteria after setting
any existing session level optgoal setting; an
explicit optgoal setting could return an optimization
criteria to its default value.
See “Default optimization criteria” for
more information.
Use the set command to enable or disable individual criteria.
For example, to enable the hash join algorithm, enter:
set hash_join 1
To disable the hash join algorithm, enter:
set hash_join 0
To enable one option and disable another, enter:
set hash_join 1, merge_join 0
Most criteria described here decides whether a particular query engine operator can be used in the final plan chosen by the optimizer. The optimization criteria are:
hash_join – determines whether the Adaptive Server query processor may use the hash join algorithm. Hash joins may consume more runtime resources, but are valuable when the joining columns do not have useful indexes or when a relatively large number of rows satisfy the join condition, compared to the product of the number of rows in the joined tables.
hash_union_distinct – determines whether the query processor may use the hash union distinct algorithm, which is not efficient if most rows are distinct.
merge_join – determines whether the Adaptive Server query processor may use the merge join algorithm, which relies on ordered input. merge_join is most valuable when input is ordered on the merge key—for example, from an index scan. merge_join is less valuable if sort operators are required to order input.
merge_union_all – determines whether the Adaptive Server query processor may use the merge algorithm for union all. merge_union_all maintains the ordering of the result rows from the union input. merge_union_all is particularly valuable if the input is ordered and a parent operator (such as merge join) benefits from that ordering. Otherwise, merge_union_all may require sort operators that reduce efficiency.
merge_union_distinct – determines whether the query processor may use the merge algorithm for union. merge_union_distinct is similar to merge_union_all, except that duplicate rows are not retained. merge_union_distinct requires ordered input and provides ordered output.
multi_table_store_ind – determines whether the query processor may use reformatting on the result of a multiple table join. Using multi_gt_store_ind may increase the use of worktables.
nl_join – determines whether the Adaptive Server query processor may use the nested-loop-join algorithm.
opportunistic_distinct_view – determines whether the query processor may use a more flexible algorithm when enforcing distinctness.
parallel_query – determines whether the Adaptive Server query processor may use parallel query optimization.
store_index – determines whether the query processor may use reformatting, which may increase the use of worktables.
append_union_all – determines whether the query processor may use the append union all algorithm.
bushy_search_space – determines whether the query processor may use bushy-tree-shaped query plans, which may increase the search space, but provide more query plan options to improve performance.
distinct_hashing – determines whether the query processor may use a hashing algorithm to eliminate duplicates, which is very efficient when there are few distinct values compared to the number of rows.
distinct_sorted – determines whether the Adaptive Server query processor may use a single-pass algorithm to eliminate duplicates. distinct_sorted relies on an ordered input stream, and may increase the number of sort operators if its input is not ordered.
group-sorted – determines whether the query processor may use an on-the-fly grouping algorithm. group-sorted relies on an input stream sorted on the grouping columns, and it preserves this ordering in its output.
distinct_sorting – determines whether the Adaptive Server query processor may use the sorting algorithm to eliminate duplicates. distinct_sorting is useful when the input is not ordered (for example, if there is no index) and the output ordering generated by the sorting algorithm could benefit; for example, in a merge join.
group_hashing – determines whether the query processor may use a group hashing algorithm to process aggregates.
index_intersection – determines whether the query processor may use the intersection of multiple index scans as part of the query plan in the search space.
The query processor will re-enable a default algorithm if all the algorithms of a relational operator are disabled. For example, if all join algorithms (nl_join, m_join, and h_join) are disabled, the query processor will enable nl_join.
The query processor can also re-enable nl_join for semantic reasons: for example, if the joining tables are not connected through equijoins.
Each optimization goal – fastfirstrow, allrows_oltp, allrows_mixed, allrows_dss – has a default setting (on or off) for each optimization criterion. For example, the default setting for merge_join is off for fastfirstrow and allrows_oltp, and on for allrows_mixed and allrows_dss. See Table 5-4 for a list of default settings for each optimization criteria.
Sybase recommends that you reset the optimization goal and evaluate performance before changing optimization criteria. Change optimization criteria only when necessary to fine-tune a particular query.
Optimization criteria |
fastfirstrow |
allrows_oltp |
allrows_mixed |
allrows_dss |
|---|---|---|---|---|
append_union_all |
1 |
1 |
1 |
1 |
bushy_search_space |
0 |
0 |
0 |
1 |
distinct_sorted |
1 |
1 |
1 |
1 |
distinct_sorting |
1 |
1 |
1 |
1 |
group_hashing |
1 |
1 |
1 |
1 |
group_sorted |
1 |
1 |
1 |
1 |
hash_join |
0 |
0 |
0 |
1 |
hash_union_distinct |
1 |
1 |
1 |
1 |
index_intersection |
0 |
0 |
0 |
1 |
merge_join |
0 |
0 |
1 |
1 |
merge_union_all |
1 |
1 |
1 |
1 |
multi_gt_store_ind |
0 |
0 |
0 |
1 |
nl_join |
1 |
1 |
1 |
1 |
opp_distinct_view |
1 |
1 |
1 |
1 |
parallel_query |
1 |
0 |
1 |
1 |
store_index |
1 |
1 |
1 |
1 |
