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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-2
Types of Cacheable Queries
settings that was in effect when this query was previously compiled, results in a cache
hit, assuming that all other criteria for a cache hit are met.
For example, consider how the compiler responds to a sequence of control statements
and query compilation requests:
CONTROL TABLE T TIMEOUT 500;
SELECT * FROM T; -- mxcmp adds "SELECT * FROM T; T TIMEOUT 500" to
cache
CONTROL TABLE T TIMEOUT -1;
SELECT * FROM T; -- mxcmp adds "SELECT * FROM T; T TIMEOUT
-1" to cache
CONTROL TABLE T TIMEOUT 500;
SELECT * FROM T; -- mxcmp hits on "SELECT * FROM T; T TIMEOUT 500"
CONTROL TABLE T TIMEOUT -1;
SELECT * FROM T; -- mxcmp hits on "SELECT * FROM T; T TIMEOUT -1"
The SQL/MX compiler responds similarly to CONTROL QUERY DEFAULT statements.
Types of Cacheable Queries
The queries that are considered for query plan caching include simple TP-style inserts,
updates, deletes, selects, and joins. Two queries are considered equivalent for the
purposes of caching if their canonical forms are the same. For query caching, the
canonical form of a query is constructed by:
•
Removing unmeaningful white space differences
•
Removing unmeaningful case differences
•
Expanding ‘*’ notation in select lists
•
Resolving all object names to fully qualified names
•
Replacing most constant literals with parameters
•
Encoding all CONTROL QUERY DEFAULT and CONTROL TABLE statements
that have been previously executed in the current SQL/MX compiler session
Query caching is restricted to only those queries whose compiled plans and plan
quality are unaffected by the actual values of their literal constants and that have a
high probability for reuse.
UPDATE STATISTICS does not affect query caching. Cacheable queries remain
cacheable, and noncacheable queries remain noncacheable with or without updating
statistics.
The SQL/MX compiler generates the same plan for many TP-style queries that are
guaranteed to return or update at most one row. The next examples are all guaranteed