Skip to main content

PURGE CACHED QUERIES (SQL)

Deletes one or more cached queries.

Synopsis

PURGE [CACHED] QUERIES

PURGE [CACHED] QUERIES BY AGE n

PURGE [CACHED] QUERIES BY TABLE table-name

PURGE [CACHED] QUERIES BY NAME class-name [, class-name]

Arguments

Argument Description
n An integer number of days since the cached query was last used, specified as a quoted string.
table-name The name of an existing table for which there are cached queries. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.
class-name A cached query class name or a comma-separated list of cached query class names. Cached query class names are case-sensitive.

Description

The PURGE CACHED QUERIES command removes defined cached queries within a specified scope:

  • PURGE CACHED QUERIES purges all cached queries in the current namespace.

  • PURGE CACHED QUERIES BY AGE n purges all cached queries in the current namespace that have not been used (prepared) within the last n days. Specifying an n value of 0 purges all cached queries in the current namespace.

  • PURGE CACHED QUERIES BY TABLE table-name purges all cached queries that reference a specified table. If a query references several tables, a single cached query is generated and listed for each of these tables. Issuing a PURGE CACHED QUERIES BY TABLE for any one of these tables purges this cached query from all of these tables.

  • PURGE [CACHED] QUERIES BY NAME class-name purges cached queries specified by cached query class name. You can specify multiple cached queries as a comma-separated list. Listed cached queries can reference different tables, but all must be within the current namespace. Cashed query names are case-sensitive.

The CACHED keyword is optional.

If the specified class-name does not exist, or the letter case specified is not correct, that class name is skipped and the command proceeds to purge the next cached query in the list; for an invalid class name no operation is performed and no error is generated. If the specified table does not have any associated cached queries, or the table does not exist, no operation is performed and no error is generated.

For further details, refer to Purging Cached Queries in the SQL Optimization Guide.

Examples

The following example purges the cached query specified by name:

PURGE CACHED QUERIES BY NAME %sqlcq.USER.cls2

The following example purges all cached queries that have not been used in the last two days:

PURGE CACHED QUERIES BY AGE "2"

See Also

Feedback