SQL/MX Comparison Guide for SQL/MP Users

Optimizer and Executor
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
5-2
Optimizer
SQL/MP Optimizer SQL/MX Optimizer
Search engine uses dynamic programming
(bottom-up optimization) to enumerate
possible join orders. Considers Cartesian
products as part of the join enumeration.
Search engine uses branch and bound
programming (top-down optimization).
Multipass design; in the pilot phase, tables are
ordered in ascending order, and large tables
are scanned last.
Optimizations considered include:
•
Three-join algorithms (nested join,
hybrid hash join, and merge join).
•
Parallel join algorithm (matching,
nonmatching).
•
Repartitioned join.
•
Aggregation and grouping in DAM
(aggregate and sort GROUP BY).
•
Unions cannot be parallelized.
•
Transitive closure; equality predicates
are rewritten to include implied
predicates.
Optimizations considered include:
•
Four-join algorithms (nested join, ordered
hash join, hybrid hash join, and merge
join). Within a join, also supports range
repartitioning.
•
Extensible query tree allows for parallel
operations anywhere in the query tree.
•
Repartitioned join.
•
Aggregation and grouping can be
executed in DAM and ESPs.
•
Unions can be parallelized.
•
Transitive closure more extensive;
extends to grouping columns, order by
columns, and column references in the
select list.
•
GROUP BY operations can occur almost
anywhere in the query tree.