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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-16
Influencing the Optimizer to Use MDAM
This IN predicate is converted into:
COL1 = 1 OR COL1 = 2 OR COL1 = 3
Consider:
SELECT * FROM T WHERE
((A = 4 AND B IN (2,5))
OR (A = 7 AND B IN (6,9)) ;
The optimizer transforms this query into these predicate sets:
(A = 4 AND B = 2) OR
(A = 4 AND B = 5) OR
(A = 7 AND B = 6) OR
(A = 7 AND B = 9)
Redundant and Contradictory Predicates
MDAM eliminates predicates that conflict with other predicates. For example, this
query shows conflicting predicates:
SELECT * FROM T1
WHERE DIMENSION_2
BETWEEN 2 AND 3 AND DIMENSION_2=1;
MDAM recognizes that these predicates conflict and removes both of them, resulting in
no table accesses (that is, DIMENSION_2 cannot be both 1 and [2 through 3]). In
addition, at run time, MDAM resolves conflicting predicates and combines overlapping
ranges and IN lists within a single disjunct.
Duplicate Rows
MDAM avoids reading the same data twice so that it does not have to do post-read
operations to accomplish duplicate elimination. MDAM combines overlapping ranges
among the disjuncts and separates the disjuncts into nonoverlapping accesses.
Sort Order
MDAM orders the retrievals in the order of the index being accessed. The order can be
ascending or descending. MDAM maintains the index order even when reading the
index backward, which satisfies ordering requirements and prevents a sort of the
query.
Influencing the Optimizer to Use MDAM
You can influence the optimizer to choose MDAM as the access path by ordering your
key columns in your index design:
•
Perform UPDATE STATISTICS on leading columns. As the UECs for leading
columns increase (and if the columns do not have predicates on them in the
query), the number of seeks to the table increases. The UECs of leading columns
are the single most important factor in MDAM access.