SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-5
Transformation of Predicates
Some WHERE clauses cannot be processed by MDAM as single predicate sets.
Usually these clauses contain one or more ORs and must be processed as multiple
predicate sets.
An IN predicate equivalent is the result when an IN predicate is converted into a series
of ORs, as follows:
COL1 IN (1, 2, 3)
This IN predicate is converted into this:
COL1 = 1 OR COL1 = 2 or COL1 = 3
Consider this query:
SELECT * FROM T WHERE
((C = 10 AND B BETWEEN 5 AND 10) OR A IN (2, 4, 5)) AND
((A = 4 AND C = 5) OR (C IN (5,10) AND (B = 5 OR A = 2))) ;
Using MDAM, the optimizer transforms this query into these six predicate sets:
(A = 4 AND B BETWEEN 5 AND 10 AND C = 10 AND C = 5)
OR (B BETWEEN 5 AND 10 AND B = 5 AND C = 10 AND C IN (5, 10))
OR (A = 2 AND B BETWEEN 5 AND 10 AND C = 10 AND C IN (5, 10))
OR (A IN (2, 4, 5) AND A = 4 AND C = 5)
OR (A IN (2, 4, 5) AND B = 5 AND C IN (5, 10))
OR (A IN (2, 4, 5) AND A = 2 AND C IN 5, 10))
SQL might choose MDAM based on the key predicates you specify in the query, but
you can force MDAM by using a CONTROL directive. The EXPLAIN plan for the query
shows when SQL uses MDAM.
Plans That Do Not Use MDAM
MDAM is not used in these cases:
•
The predicate contains no key columns.
•
A join predicate is used for a key-sequenced merge join. For more information on
key-sequenced merge joins, see Key-Sequenced Merge Join
on page 3-27 and be
aware that MDAM can be used for reading the outer table.
•
An OR is used to connect a key predicate with a nonkey predicate.
In this example, the query would not use MDAM because an OR connects the key
predicate UNIQUE2 = 5 with the nonkey predicate FOUR = 2:
SELECT * FROM
FROM TENKTUP1
WHERE UNIQUE2 = 4
OR (UNIQUE2 = 5 OR FOUR = 2);
However, MDAM would be considered for this query because an AND is used to
connect the key predicate UNIQUE2 = 5 with the nonkey predicate FOUR = 2:
SELECT * FROM
FROM TENKTUP1