SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-8
Transformation of Predicates
With MDAM, only rows that match the values in the value list are read. Unlike OR
optimization, when a predicate has more than one element in the value list, MDAM
eliminates any duplicate values at run time, not at compile time. Because this is done
before any tables are accessed, there is no performance penalty. MDAM processing
appears as an MDAM predicate set in the EXPLAIN plan.
For more information, see Using OR Operators in Predicates on page 3-22.
IN Predicates With Subqueries
If the expression of an IN predicate is a subquery, then SQL transforms the IN
predicate into an “= ANY” subquery. For example, this predicate
DEPT_NUM IN ( SELECT DNUM ... )
is transformed into:
DEPT_NUM = ANY ( SELECT DNUM ... )
SQL optimizes the execution of noncorrelated ANY, ALL, and SOME subqueries. The
SQL executor builds a table in memory that contains the result of the subquery. In the
preceding example, SQL uses the key DNUM to search the in-memory table for values
that match the DEPT_NUM value retrieved by the outer query.
SQL does not generate parallel plans for IN subqueries. The use of EXISTS or a join
operation, which can both support parallel plans, might be more efficient than using an
IN predicate in a subquery.
Transformations Related to Joins
The optimizer uses a feature called Query Rewrite to transform user-specified search
conditions for faster execution of join queries. These automatic transformations are
especially useful in decision support applications—applications that allow you to invoke
ad hoc queries and often can consume large amounts of time and disk space.
The transformation of search conditions can provide these benefits:
•
Reduce the number of rows involved in a join operation
•
Present a broader range of alternative plans during join optimization
•
Remove redundant or unnecessary operations
•
Make better use of existing access paths
Applications that benefit most include those that have one or more of these
characteristics:
•
SQL DML generated by software
•
Extensive use of shorthand views
•
Applications ported to NonStop SQL/MP from another database management
system