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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-8
SYSTEM_DEFAULTS Table Settings for Query Plan
Caching Attributes
the new entry is not added to the cache, and no resident entries can be displaced.
Because the query plan cache feature is transparent, no error messages are
issued.
If QUERY_CACHE_MAX_VICTIMS is later set to a nonzero value, replacement
resumes as usual. The number of entries that the cache can hold depends on the
size of the cache and the size of the cached plans. The system-defined default
setting limits the number of cache entries that can be displaced to 10 cache
entries.
•
QUERY_CACHE_REQUIRED_PREFIX_KEYS
System-defined default setting: 255
Allowable values: 0 to 255
This attribute specifies how many and which columns of a composite primary or
partition key are required for an equality predicate to be considered cacheable. If
the attribute is set to a value greater than the number of columns in a composite
key, all columns of the key are required. The system-defined default setting is 255,
which means that only complete primary or partition key equality predicates are
cacheable. To avoid compromising query plan quality, it is recommended that you
keep the system-defined default setting of 255.
The value 0 means that the presence of any one column of a composite primary or
partition key in an equality key predicate is sufficient to make that predicate
cacheable. A value n that is greater than zero but less than the number of columns
in the key indicates that the first n columns of the key are required to be present in
a key predicate for that predicate to be considered cacheable.
Suppose that the QUERY_CACHE_REQUIRED_PREFIX_KEYS setting is 1, and
the table T has a composite primary key consisting of columns (a, b, and c). With
the setting of 1, provided that the first column of the key (a) is an equality
predicate, the query is cacheable. If the
QUERY_CACHE_REQUIRED_PREFIX_KEYS setting is 2, the only valid prefixes
of (a, b, c) are (a, b) and (a, b, c). That is, these queries are cacheable:
SELECT * FROM T WHERE a=1 AND b = 20;
DELETE FROM T WHERE (a,b,c)=(9,909,10);
However, these queries are not cacheable:
SELECT * FROM T WHERE a=77;
DELETE FROM T WHERE (b,c)=(1,23);
This attribute can be used to force certain noncacheable queries into cacheable
queries. In the previous example, SELECT * FROM T WHERE A=77 is not
cacheable because its equi-predicate specifies only the first of a three-column key.
To make it cacheable, specify CONTROL QUERY DEFAULT
QUERY_CACHE_REQUIRED_PREFIX_KEYS ‘1,’ and the query becomes
cacheable.