SQL/MX Comparison Guide for SQL/MP Users

Optimizer and Executor
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
5-3
Executor
Executor
The executor in NonStop SQL/MX has also been rewritten and takes advantage of
these features:
•
Completely no-waited operations
•
More work done in the DAM
•
Multiple forms of parallelism
•
Scalability through parallelism on multiple levels
Costing and statistics:
•
Uses captured statistics to estimate
the number of rows. Consists of a
number of values—UEC, second high
and second low values—on a per-
column basis.
•
Numeric data requires data be
uniformly distributed (between the
second high and second low values).
•
Costing based on simple arithmetic.
•
Cost is represented by a single
floating point value.
Costing and statistics:
•
Histograms are used to represent data
distribution, not just second high and
second low values. Data distribution is
divided into intervals. For each interval,
NonStop SQL/MX stores the number of
values and the UEC.
•
Multicolumn UEC and row count.
•
Histograms can capture the true
distribution, given enough histogram
intervals.
•
Costing aligned to concurrency and
parallelism.
•
Cost is represented as cost vectors
composed of resource units.
Influencing query plan:
•
Force plans through the CONTROL
directive (table scope).
•
Change a few defaults with CONTROL
directives on a table basis.
•
Update statistics.
•
Modify the table, such as adding
indexes.
Influencing query plan:
•
Force plans through the CONTROL
QUERY SHAPE statement (query tree
scope).
•
Change user defaults in several ways: per
query tree, table basis, system or session
basis.
•
Update statistics.
Transformations within queries:
•
User must know how the optimizer
handles predicates so that proper
query design yields an optimal plan.
•
Some predicate transformations done.
•
Predicates are pushed down as far as
possible.
Optimizer internally handles transformations
within queries. Some transformations include:
•
Unconditional predicate transformations
(for example, constant folding).
•
Subqueries transformed to joins.
•
Predicates rewritten and pushed down as
far as possible (more than in NonStop
SQL/MP).
•
Syntactic and semantic sort elimination.
SQL/MP Optimizer SQL/MX Optimizer