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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-15
How MDAM Processes Queries
•
Does not read the same row twice
•
Maintains sort order
Intervening Range Predicates
An intervening range predicate occurs when another key column predicate follows the
first predicate:
A > 5 AND B = 2
MDAM processes range predicates by stepping through the existing values for the
column on which the range has been specified. Data outside the bounds is not read
from disk or handled in any way.
Missing Key Predicates
When no predicates have been specified for a leading or intervening key column,
MDAM can still use the subsequent columns for keyed access. MDAM is most effective
when a skipped column (no predicate) has a low UEC. Consider this query where the
index contains DEPT, SALES_DATE, ITEM_CLASS, and STORE, in that order:
SELECT SALES_DATE, SUM(TOTAL_SALES)
FROM SALES
WHERE SALES_DATE BETWEEN DATE'06/01/95' AND DATE'06/30/95'
AND ITEM_CLASS=20 AND STORE=250 GROUP BY DEPT, SALES_DATE;
Notice that no predicate was provided for the first key column, DEPT. Without MDAM,
the compiler would have to resort to a full table scan for the query. MDAM treats the
missing predicate for DEPT as an implied range of MIN_VALUE to MAX_VALUE
(including NULL values). These values are respectively the minimum and maximum
permissible values supported for the data type of the missing key column.
OR Predicates
MDAM works with OR operators to:
•
Avoid creating duplicates:
A IN (1, 2, 3, 2)
MDAM reads 1, 2, 3. The last 2 is not read.
•
Allow WHERE clauses that are not in disjunctive normal form; that is, WHERE
clauses can have more than one level of OR operations.
IN Lists
An IN predicate equivalent is the result when an IN predicate is converted into a series
of ORs:
COL1 IN (1, 2, 3)