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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-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.