SQL/MX 2.x Reference Manual (H06.04+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—540440-003
10-60
Query Plan Caching
For a full discussion of query plan caching, see the SQL/MX Query Guide.
QUERY_CACHE_REQUIRED_
PREFIX_KEYS
Set to a value between 0 and 255. 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 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 0
(zero) but less than the number of columns in the key
indicates that the first n columns of the key must be
present in a key predicate for that predicate to be
considered cacheable.
For additional information about this setting, see the
SQL/MX Query Guide.
The default setting is 255, which means that only
complete primary or partition key equality predicates
are cacheable. To avoid compromising query plan
quality, You should keep the system-defined default
setting of 255.
QUERY_CACHE_STATEMENT_
PINNING
Set to ON, OFF, or CLEAR. Controls whether queries
are entered into the cache as pinned or unpinned. You
might have important, compile-time critical queries that
you want to ensure are in the cache when needed.
When a query is pinned in the cache, it usually cannot
be displaced from the cache unless the cache becomes
full of pinned queries. In this case, the least recently
used pinned entries also become displaceable.
The value CLEAR means that all subsequent query
cache entries are unpinned, and all pinned entries in
the cache are also unpinned.
The value ON means that all subsequent query cache
entries into the cache are pinned.
The default setting, OFF, means that all subsequent
query entries into the cache are unpinned.
Attribute Setting