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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-4
Examples of Queries That Are Not Cacheable
•
Math functions (ABS, ATAN, ATAN2, CEILING, COS, COSH, DEGREES, EXP,
FLOOR, LOG, LOG10, PI, POWER, RADIANS, SIGN, SIN, SINH, SQRT, TAN,
TANH are cacheable):
SELECT ABS(i), ATAN(10), ATAN2(x,y), CEILING(r), COS(i),
COSH(i), DEGREES(i), EXP(i), FLOOR(i), LOG(i), LOG10(i),
PI(), POWER(b,e), RADIANS(i), SIGN(i), SIN(i), SINH(i),
SQRT(i), TAN(i), TANH(i) FROM T;
•
Replace functions are cacheable:
UPDATE T SET job=replace(job, 'IM', 'IT') WHERE K=?;
•
Two-table single row joins are cacheable. This two-table single row join is
cacheable, assuming table T has K as its primary (or partition) key and table U has
J as its primary (or partition) key:
SELECT * FROM T, U WHERE (K,J)=(?,?) AND K=J;
All other functions not listed here are not cacheable.
The SQL/MX compiler is guaranteed to generate the same plans for similar TP-style
queries that differ only in their literal values. These examples yield the same plan:
DELETE FROM T WHERE K=9999;
DELETE FROM T WHERE K=7;
DELETE FROM T WHERE K=?;
The next three examples also yield the same plan:
UPDATE T SET C=35 WHERE K=14;
UPDATE T SET C=7 WHERE K=31;
UPDATE T SET C=? WHERE K=?;
Query plan caching effectively treats most literal values in TP-style queries as wild
cards when comparing a query against previously cached queries. However, not all
literal values are treated as wild cards. For example, the pattern and escape literals in
this example are not treated as wild cards during query comparison:
SELECT * FROM T WHERE K=1 AND S LIKE '\_C%' escape '\';
The previous query is not considered the same as the next query during query
comparison:
SELECT * FROM T WHERE K=1 AND S LIKE '\$C%' escape '\';
Examples of Queries That Are Not Cacheable
•
Queries that have only LIKE predicates are not cacheable:
SELECT * FROM T WHERE S LIKE 'c%'; --is not cacheable