Chat now with support
Chat with Support

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.

 

Related Topics

Related Documents