Query optimizer

The query optimizer provides speed and efficiency for online transaction processing (OLTP) and operational decision-support systems (DSS) environments. You can choose an optimization strategy that best suits your query environment.

The query optimizer is self-tuning, and requires fewer interventions than versions of Adaptive Server Enterprise ealier than 15.0. It relies infrequently on worktables for materialization between steps of operations; however, the query optimizer may use more worktables when it determines that hash and merge operations are more effective.

Some of the key features in the release 15.0 query optimizer include support for:

New optimization techniques and query execution operator supports that enhance query performance, such as:
- On-the-fly grouping and ordering operator support using in-memory sorting and hashing for queries with group by and order by clauses
- hash and merge join operator support for efficient join operations
- index union and index intersection strategies for queries with predicates on different indexes
Table 1-1 is a list of optimization techniques and operator support provided in Adaptive Server Enterprise. Many of these techniques map directly to the operators supported in the query execution. See “Lava query execution engine”.
Improved index selection, especially for joins with or clauses, and joins with and search arguments (SARGs) with mismatched but compatible datatypes
Improved costing that employs join histograms to prevent inaccuracies that might otherwise arise due to data skews in joining columns
New cost-based pruning and timeout mechanisms in join ordering and plan strategies for large, multiway joins, and for star and snowflake schema joins
New optimization techniques to support data and index partitioning (building blocks for parallelism) that are especially beneficial for very large data sets
Improved query optimization techniques for vertical and horizontal parallelism. See Chapter 5, “Parallel Query Processing.”
Improved problem diagnosis and resolution through:
- Searchable XML format trace outputs
- Detailed diagnostic output from new set commands. See Chapter 3, “Displaying Query Optimization Strategies and Estimates.”

**Table 1-1: Optimization operator support**
operator	Description
hash join	Determines whether the query optimizer may use the hash join algorithm. hash join may consume more runtime resources, but is 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 optimizer may use the hash union distinct algorithm, which is inefficient if most rows are distinct.
merge join	Determines whether the query optimizer 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 query optimizer 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 optimizer 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.
nested-loop-join	The nested-loop-join algorithm is the most common type of join method and is most useful in simple OLTP queries that do not require ordering.
append union all	Determines whether the query optimizer may use the append algorithm for union all.
distinct hashing	Determines whether the query optimizer 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 query optimizer 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 optimizer 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 query optimizer 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 optimizer may use a group hashing algorithm to process aggregates.

Technique	Description
multi table store ind	Determines whether the query optimizer may use reformatting on the result of a multiple table join. Using multi table store ind may increase the use of worktables.
opportunistic distinct view	Determines whether the query optimizer may use a more flexible algorithm when enforcing distinctness.
index intersection	Determines whether the query optimizer may use the intersection of multiple index scans as part of the query plan in the search space.