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.
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
