SQL/MX Comparison Guide for SQL/MP Users

HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
5-1
5 Optimizer and Executor
Both the compiler and the executor for NonStop SQL/MX are different from NonStop
SQL/MP. Some highlights of the new compiler include:
•
Rule and cost-based optimizer
•
Optimization of complex relational expressions
•
Use of histogram statistics
•
Extensible cost model
Some highlights of the rewritten executor include:
•
Data-flow, scheduler-driven task model
•
Increased parallelism
Optimizer
The optimizer is the stage in the SQL compiler that optimizes the query and
determines the optimal query execution plan. Many design differences in NonStop
SQL/MX occur before optimization. During the normalization phase, these tasks are
performed:
•
Subquery transformation into joins
•
Constant folding (for example, b < 0 * c becomes b < 0)
•
Predicates pushed down or pulled up
•
Other unconditional transformations (for example, left joins transformed to inner
joins)
This table highlights the differences between the SQL/MP and SQL/MX optimizers: