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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-3
Examples of Cacheable Expressions
to update or return at most one row if table T has K as its primary key column. These
are all cacheable queries:
DELETE FROM T WHERE K=1;
UPDATE T SET C=1 WHERE K=2;
SELECT * FROM T WHERE K=1;
INSERT INTO T(K,C) VALUES(2,1);
Examples of Cacheable Expressions
•
Dynamic parameters are cacheable
UPDATE T SET C=? WHERE K=?;
•
Arithmetic expressions are cacheable
SELECT m + n - p * q / r FROM T WHERE K=?;
•
Aggregate functions (MAX, MIN, SUM, AVG, COUNT are cacheable):
SELECT MAX(i), MIN(i), SUM(i), AVG(i), COUNT(DISTINCT i)
FROM T WHERE K=?;
•
Concatenation is cacheable
UPDATE T SET D=D||'1', E=CONCAT(E,'z') WHERE K=?;
•
String functions (CHAR LENGTH, OCTET LENGTH, LCASE, LOWER, UCASE,
UPPER, UPSHIFT are cacheable):
SELECT CHAR LENGTH(d), OCTET LENGTH(d), LCASE(d), LOWER('A'),
UCASE(d), UPPER('a'), UPSHIFT('b') FROM T;
•
Datetime functions (CONVERTTIMESTAMP, JULIANTIMESTAMP, CURRENT
TIMESTAMP, CURRENT DATE, CURRENT TIME, CURRENT, NOW,
DATEFORMAT, DAY, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR,
FIRSTDAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, QUARTER,
SECOND, WEEK, YEAR FUNCTIONS are cacheable):
UPDATE T SET
TS=CONVERTTIMESTAMP(JULIANTIMESTAMP(CURRENT_TIMESTAMP))
WHERE K=?;
SELECT CURRENT_DATE, CURRENT_TIME, DATEFORMAT(NOW(),USA),
DATEFORMAT(NOW(),EUROPEAN), DAY(CURRENT), DAYNAME(NOW(),
DAYOFMONTH(NOW()), DAYOFWEEK(NOW()), DAYOFYEAR(NOW()),
FIRSTDAYOFYEAR(NOW()), HOUR(NOW()), MINUTE(NOW)),
MONTH(NOW()), MONTHNAME(NOW()), QUARTER(NOW()),
SECOND(NOW()), WEEK(NOW()), YEAR(NOW()) FROM (VALUES(1)) AS
T;
•
Case expressions are cacheable:
SELECT CASE i WHEN 3 THEN 'YES' ELSE 'NO' END FROM T;