SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Compiling and Executing a Query

HP NonStop SQL/MX Query Guide—523728-003

1-4

Parsing, Binding, and Normalizing

by the optimizer go into the search space, where they are stored in search

memory.

•

Principle of Optimality

In this strategy, the optimizer starts at the top of the normalized query tree and

breaks the optimization tasks into smaller tasks (or subtasks). Depending on the

physical properties of the operators, the optimizer combines the optimal solutions

of the subtasks for the completed plan.

•

Avoiding Search Space Overload

The search space contains all possible plans that the optimizer considers. Because

the optimizer tries for as many alternatives as possible per node in the query tree,

a risk exists that you can inundate the search space with all the possibilities.

Equivalent expressions are organized into groups. Two expressions are in the

same group if one can be derived from the other by the application of rules. The

organization of the search memory reduces the search space dramatically. To

influence the amount of optimization that the optimizer performs in trying to find the

best plan, you can set the OPTIMIZATION_LEVEL attribute. For more information

about setting the OPTIMIZATION_LEVEL attribute, see Section 4, Reviewing

Query Execution Plans.

•

Rule-Based Solutions

In addition to reviewing costs associated with operations, the optimizer also uses

rules. Optimization rules define how a query tree or one of its subtrees can be

transformed into a semantically equivalent query tree. These rules separate the

logical and physical expressions, as follows:

What the optimizer does to find a solution. Transformation rules transform

one logical expression into a different, semantically equivalent logical

expression. Logical expressions do not have a cost associated with them and

do not have all their physical properties specified; that is, instead of using the

query tree suggestions, the optimizer creates another tree. For example, the

join of table T1 and table T2 should yield an equivalent logical expression

when transformed to a join of table T2 and table T1.

How the optimizer finds the solution. Implementation rules transform a

logical expression into a semantically equivalent expression whose root node

join

VST012.vsd