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:

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 MergeJoin operator support for efficient join operations
- index union and index intersection strategies for queries with predicates on different indexes
The complete list of optimization techniques and operator support in Adaptive Server is listed in Table 1-1. Many of these techniques map directly to the operators supported in the query execution. See “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, multi-way 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 4, “Parallel Query Processing,” for more details.
Improved problem diagnosis and resolution through:
- Searchable XML format trace outputs
- Detailed diagnostic output from new set commands. See Chapter 11, “Query Processing Metrics,” for more details.

**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.