SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)
Query Plan Caching
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
6-5
Examples of Queries That Are Not Cacheable
However, a LIKE predicate conjunct of a key equipredicate is cacheable:
SELECT * FROM T WHERE K=? AND S LIKE 'c%';
Queries that have only OR predicates are not cacheable:
SELECT * FROM T WHERE a=1 OR b=2; -- is not cacheable
However, an OR predicate conjunct of a key equipredicate is cacheable:
SELECT * FROM T WHERE K=? and (a=1 OR b=2);
Queries that have BETWEEN predicates are not cacheable:
SELECT * FROM T WHERE a BETWEEN 1 AND 9; -- is not cacheable
SELECT * FROM T WHERE K=? AND (a BETWEEN 1 AND 9); -- is not
cacheable
Queries that have only IN predicates are not cacheable:
SELECT * FROM T WHERE i IN (1,2); -- is not cacheable
However, an IN predicate conjunct of a key equi-predicate is cacheable:
SELECT * FROM T WHERE K=? AND i IN (1,2); -- is cacheable
Also, a single-value key IN predicate is cacheable:
SELECT * FROM T WHERE K IN (1);
Queries that have only NOT predicates are not cacheable:
SELECT * FROM T WHERE NOT(i IN (1,2)); -- is not cacheable
SELECT * FROM T WHERE NOT (K<>1); -- is not cacheable
However, a NOT predicate conjunct of a key equipredicate is cacheable:
SELECT * FROM T WHERE K=? AND NOT (i IN (1,2));
Queries that have function calls other than those listed in Examples of Cacheable
Expressions on page 6-3 are not cacheable:
SELECT * FROM T WHERE K=? AND SUBSTRING(c,1,1)='z'; --is not
cacheable
Queries that have subqueries are not cacheable:
SELECT * FROM T WHERE K=? AND N=(SELECT MAX (b) FROM t); --is
not cacheable
Queries that have relational unions, intersections, differences, divisions, and table-
value stored procedures are not cacheable:
SELECT a FROM t UNION SELECT b FROM s; -- is not cacheable
Queries that have compound statements or rowsets are not cacheable.
Queries that have transpose, sample, sequence, offset, or other data mining
predicates are not cacheable.
TP663851.fm Page 5 Wednesday, January 30, 2013 5:37 PM