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

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-10
Improving Query Performance
Programming Manual for C and COBOL, SQL/MX Programming Manual for Java, and
the SQL/MX Installation and Management Guide have tips and guidelines to help
identify potential performance problem areas.
Using OR Operators in Predicates
For a narrow subset of queries with OR operations, SQL/MX uses a feature called OR
optimization. OR optimization uses more than one access path to obtain the data and
eliminates duplicate predicates to produce the result. In many cases, OR optimization
results in the query running significantly faster. While partitioning is not required for OR
optimization benefits to take effect, performance benefits might be further enhanced
with partitioned tables.
•
OR optimization and application example
OR optimization might be useful in querying large transaction tables where the
expected output is only a few rows.
•
MDAM and OR optimization
MDAM is another feature that works with OR operators. It provides some benefits
that the OR optimization feature does not:
°
MDAM eliminates duplicate key values in OR predicates at run time. It does so
before SQL/MX accesses any tables, so there is no performance penalty.
°
MDAM can process multiple tables in a query. It can be used on the inner and
outer tables of nested joins and on the outer tables of sort merge, hash, and
key-sequenced merge joins.
°
WHERE clauses need not be in disjunctive normal form.
MDAM is enabled by default.
•
Choosing optimized OR plans
SQL might use an optimized OR plan when all these conditions are satisfied:
°
Two or more search conditions connected by OR operators.
°
Each search condition contains predicates used as keys on an index. That is,
the predicates involve columns that belong to the key prefix of an index.
Note. OR optimization in SQL/MX has a more restrictive set of conditions than its SQL/MP
counterpart.
Note. A search condition in disjunctive normal form has only one level of OR operations;
for example, (P1 and P2) or (P4 and P5) or (P6 and P7 and P8). A search condition not in
normal disjunctive form can be transformed into one. For example, (A or B) and C can be
changed to (A and C) or (B and C). A search condition in conjunctive normal form has one
level of AND operations; for example, P1 and P2 and P3.