SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-6
Transformation of Predicates
WHERE UNIQUE2 = 4
OR (UNIQUE2 = 5 AND FOUR = 2);
Transformation of LIKE Predicates
A LIKE predicate searches for rows that match a pattern. When using LIKE against a
positioning column of an index, if the match is on a leftmost matching string (a literal
beginning with anything other than the pattern match symbol, % or _), SQL transforms
the statement into an equivalent range predicate.
For example, this request contains a leftmost matching string (the leftmost characters
are stated explicitly). Suppose that the column C contains only uppercase letters:
C LIKE "ABC%"
SQL transforms this clause to this:
C >= "ABC " AND C <= "ABCD "
This query retrieves all names that start with the string “CH” (CHARLES, CHRIS, and
so on):
SELECT NAME, PHONE_NUMBER
FROM PHONE_BOOK
WHERE NAME LIKE "CH%"
SQL transforms the preceding query into this:
SELECT NAME, PHONE_NUMBER FROM PHONE_BOOK
WHERE NAME LIKE "CH%"
AND NAME >= "CH"
AND NAME < "CI"
With this modification, SQL can take advantage of an index on NAME (if one exists)
and use values in the predicates as the begin and end keys to the index:
NAME >= "CH" and NAME < "CI"
Thus, SQL retrieves only those rows that are alphabetically equal to or greater than CH
but before CI.
SQL does not transform a LIKE predicate if there is a wild-card character (% or _) as
the leftmost character of a column value in the predicate.
SQL does not transform a LIKE predicate if there is a collation involved.
When a LIKE predicate is present on a key column and the column value does not
start with a wild card, the predicate becomes a candidate for MDAM optimization.
When MDAM is chosen, the transformed predicate shows as an MDAM predicate set
in the EXPLAIN plan.