SQL Optimizer for SAP ASE 3.9.1 - Installation Guide

Criteria

The Optimization Criteria are session level settings. Each criteria represents a specific algorithm or relational techniques that the Adaptive Server Optimizer may choose to use when it is retrieving the query plan. They are used to fine-tune the Adaptive Server optimizer to provide the best performance for your database environment.

Although you may specify that Adaptive Server optimizer use a specific algorithm or relational technique, the optimizer may not choose what you have specified.

Join

Optimization Criteria	Description
hash_join	Specify that the Adaptive Server optimizer uses the hash join algorithm. This is valuable when a large number of rows satisfy the join condition or when the joining columns do not have useful indexes. However, the hash join algorithm may consume more run time resources that other join algorithms.
nl_join	Specify that the Adaptive Server optimizer uses the nested-loop-join algorithm.
merge_join	Specify that the Adaptive Server optimizer uses the merge join algorithm. This relies on ordered input and is most valuable when the input is ordered on the merge key.

Union Distinct

Optimization Criteria	Description
merge_union_distinct	Specify that the Adaptive Server optimizer uses the merge algorithm for the UNION ALL. It is similar to merge_union_all except that duplicate rows are eliminated.
hash_union_distinct	Specify that the Adaptive Server optimizer uses the hash distinct algorithm. This will be inefficient if most of the rows are distinct.

Union All

Optimization Criteria	Description
append_union_all	Specify that the Adaptive Server optimizer uses the append union all algorithm.
merge_union_all	Specify that the Adaptive Server optimizer uses the merge algorithm for the UNION ALL. This means that it will maintain the ordering of the result row from the union input.

Group By

Optimization Criteria	Description
group_sorted	Specify that the Adaptive Server optimizer uses an on-the-fly algorithm. This algorithm relies on an input stream sorted on the grouping column and preserves this order in its output.
group_hashing	Specify that the Adaptive Server optimizer uses the use a group hashing algorithm to process aggregates.

Distinct

Optimization Criteria	Description
distinct_sorted	Specify that the Adaptive Server optimizer uses a single-pass algorithm to eliminate duplicate rows. This algorithm relies on an ordered input stream.
distinct_sorting	Specify that the Adaptive Server optimizer uses the sorting algorithm to eliminate duplicate rows. This algorithm is useful when the input is not ordered.
opportunistic_distinct _view	Specify that the Adaptive Server optimizer uses a more flexible algorithm when enforcing distinctness.
distinct_hashing	Specify that the Adaptive Server optimizer uses the hashing algorithm to eliminate duplicates, which is very efficient when there are only a few distinct values in comparison to the number of rows.

Individual

Optimization Criteria	Description
store_index	Specify that the Adaptive Server optimizer uses the reformatting, which may cause an increase in the number of worktables used.
parallel_query	Specify that the Adaptive Server optimizer uses parallel query optimization.
index_intersection	Specify that the Adaptive Server optimizer uses the intersection of multiple index scans as part of the query plan in search space.
multi_table_store_ind	Specify that the Adaptive Server optimizer uses reformatting on the result of a multiple table join.
bushy_space_search	Specify that the Adaptive Server optimizer uses bushy-tree-shaped query plans, which may cause an increase in the search space, but may provide more query plan options to improve performance.
advanced_aggregation	Specify that the Adaptive Server optimizer uses eager aggregation.
replicated_partition	Specify that the Adaptive Server optimizer uses replicated partitioning when multiple scans of the same table in different threads can improve the performance of nested loop joins.
group_inserting	Specify that the Adaptive Server optimizer uses the group by aggregation algorithm. This algorithm generates a clustered index work table on the grouping columns and inserts rows into the table in order to evaluate the aggregate.

Note: The criteria that are marked with an asterisk are counted in the Number of forces selected on the Quota Preferences page.

Optimization Time and Parallel	Description
plan opttimeoutlimit	Specify that the Adaptive Server optimizer uses the optimization timeout limit to restrict the amount of time it takes to optimize a query. Note: The default value is 60 percent. You can specify a value from 0 to 1000.
resource_granularity	Specify that the Adaptive Server optimizer uses max resource granularity to set the amount of total memory allocated to a single query.
repartition_degree	Specify that the Adaptive Server optimizer uses repartition degree to suggest the maximum number of worker processes the query processor uses to partition a data stream.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem