Query optimizer

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

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

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

Table 1-1: Optimization techniques and operator support

Operator

Description

hash join

This physical operator supports 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

This physical operator supports the hash union distinct algorithm, which is used to remove duplicates from multiple data sources. It is used for the SQL UNION operator, as well as when removing duplicate RIDs from multiple index scans in an OR optimization. This operator is most effective when few distinct values exist, compared to the number of rows.

merge join

This physical operator supports 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

This physical operator supports 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

This physical operator supports 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

This physical operator supports the nested-loop-join algorithm. It is the most common type of join method and is most useful in simple OLTP queries that do not require ordering.

append union all

This physical operator supports the append algorithm for union all, which is cheaper than the merge union all operator, since no ordering is required for inputs and, as a result, is used when no output ordering is required.

distinct hashing

This physical operator supports a hashing algorithm to eliminate duplicates, which is very efficient when there are few distinct values compared to the number of rows.

distinct sorted

This physical operator supports 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

This physical operator supports 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

This physical operator supports 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

This physical operator supports a group hashing algorithm to process aggregates.

Technique

Description

multi table store ind

Determines whether the query optimizer may use store index operator on the result of a multiple table join. Using multi table store ind may increase the use of worktables.

opportunistic distinct view

This physical operator supports a more flexible algorithm when enforcing distinctness. The operator could be used with flattened EXISTS subqueries as well as DISTINCT views or SELECT DISTINCT queries.

index intersection

This physical operator supports the intersection of multiple index scans as part of the query plan in the search space.

store index

This physical operator supports the store index algorithm (sometimes known as reformatting), which dynamically creates an index on the project restrict of a scan so that a more efficient nested loop index scan operation can be used when no useful index exists.

group inserting

This physical operator supports the group by aggregation algorithm that creates a clustered index work table on the grouping columns and evaluates the aggregate by inserting rows into the work table.

advanced aggregation

This technique attempts to reduce the number of tuples processed by joins by partially evaluating aggregates prior to joins. Also, this technique evaluates partial aggregates on each side of a union, rather than processing all the rows of a union prior to aggregating.

bushy space search

This technique increases the search space to look at more plans that could possibly improve performance. This may increase compilation time.

replicated partitioning

This technique applies only to parallel plans in which the performance of parallel nested loop joins could be helped by multiple scans of the same table in different threads.