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

Compiling and Executing a Query

HP NonStop SQL/MX Query Guide—523728-003

1-5

Query Plan Caching

is a physical operator. Recursive application of implementation rules result in a

query execution plan that consists only of physical operators. Such plans or

subplans can be executed by the executor because they have physical

properties, and their cost can be estimated by the optimizer. The

implementation rule phase refines the earlier rules that result in physical

nodes. For more information about logical and physical operators, see

Section 5, Forcing Execution Plans.

Implementation rules reduce a problem into one or more small subproblems by

making a plan decision. This rule follows the Principle of Optimality, which

states: “Combine optimal partial solutions to form an optimal solution to the

complete problem.” The next figure shows an implementation rule that

transforms a join of A and B into a hash join of A and B. The principle of

optimality states that the optimal subplans for A and B remain the same in the

plan that uses a hash join.

•

Multipass Optimization

The optimizer uses cost-based pruning by supporting a multipass optimization

technique. A pilot phase during preoptimization orders tables in ascending order,

based on estimated row count; that is, larger tables are scanned last. During the

first pass, only those rules that are necessary to generate a feasible plan (with a

reasonable cost) are enabled. Subsequent passes can then use the costs

generated in previous passes as an upper cost bound, allowing for more cost-

based pruning. This strategy has the effect of generating the optimal plan while

exploring a smaller search space and, as a result, reducing compile time.

The multipass optimization also enables error recovery. If an error occurs during

the second or subsequent pass, the plan generated from the first pass is returned.

Query Plan Caching

The SQL/MX compiler provides the ability to cache the plans of certain queries. Use

query plan caching in an environment where similar queries are generated, compiled,

executed, and SQL compilation time is significant compared to SQL execution time.

This feature improves the performance of the compiler when the plan can be produced

from cache rather than through a full compilation. The queries that can be considered

for plan caching include simple TP-style inserts, updates, deletes, selects, and joins.

join

hash join

VST013.vsd