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

Compiling and Executing a Query

HP NonStop SQL/MX Query Guide—523728-003

1-3

Parsing, Binding, and Normalizing

The initial steps in the compile process—parsing, binding, and normalizing—prepare

the query for the optimizer. Before query optimization begins, the query tree that is

produced by the parser is bound with information from the metadata. Certain query

processing instructions in addition to default values needed for optimizing the query

are processed. If you have made changes to the default settings, the parser reads the

adjusted values from the SYSTEM_DEFAULTS Table. All subqueries are eliminated or

transformed into joins or semi-joins. In addition, certain predicates are rewritten and

pushed down for earliest possible evaluation in the query tree. If the predicates are

pushed all the way down to the scan level, they are executed in HP NonStop Data

Access Manager (DAM). If the predicates are pushed down to a join, they are executed

in the master process or ESP process. The binding and normalization phases result in

a normalized query tree that is provided as input to the optimizer.

Optimizing Queries

SQL/MX uses a unique combination of optimization strategies to optimize queries:

•

Branch and Bound Programming

This technique consists of a top-down approach that starts with an query tree of

logical, relational expressions. Logical expressions contain relational operators that

do not denote an implementation. Examples are join, group by, and scan. Using

optimization rules, the optimizer makes a decision about the execution method of

the top operator in the tree. The optimizer generates optimal solutions for the

descendants of the top operator and combines those solutions with the execution

method (physical operator) of the top node. Physical operators are relational

operators that specify the actual implementation or run-time algorithm. Examples

are merge join, hash group by, and file scan.

The first feasible solution that is found will from then on form an upper cost bound.

Any solution that exceeds the cost bound is discarded immediately, and the

optimizer remembers not to pursue that solution further. Alternative solutions found

join

join scan

scan

VST011.vsd