SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-004

8-134

QUERYCACHE Function

Considerations for QUERYCACHE

Examples of QUERYCACHE

The query plan cache automatically collects statistics regarding its use. When invoked,

the QUERYCACHE table-valued stored function collects and returns the current state

of these statistics in a single row table. The statistics are reinitialized when an mxcmp

session is started and each mxcmp session maintains an independent set of statistics.

The QUERYCACHE function is an SQL/MX extension.

The QUERYCACHE function can be specified as a table reference (table) in the

FROM clause of a SELECT statement if it is preceded by the keyword TABLE and

surrounded by parentheses. The syntax for the QUERYCACHE function has no

parameters.

In a dynamic environment (that is, MXCI, MXCS, JDBC, or dynamic SQL), the

QUERYCACHE function returns the statistics of the query plan cache of the mxcmp

associated with the dynamic session. In a static environment (that is, statically

compiled embedded SQL), the QUERYCACHE function returns zero rows because at

runtime there is no associated mxcmp.

Considerations for QUERYCACHE

Using QUERYCACHE and DISPLAY_QC

The result of the QUERYCACHE function can be generated and displayed either by

using the QUERYCACHE function or the DISPLAY_QC command. The DISPLAY_QC

command provides a subset of the information displayed by the QUERYCACHE

function. The output of the QUERYCACHE function and DISPLAY_QC command is

machine-readable format. See the DISPLAY_QC Command on page 4-19.

Result of the QUERYCACHE Function

The result table of the QUERYCACHE function describes the query plan caching

information for certain SELECT, INSERT, DELETE, UPDATE, or join statements. For

QUERYCACHE ()