SAVE_PLANS

Creates optimizer-generated directed queries from the most frequently executed queries, up to the maximum specified.

Creates optimizer-generated directed queries from the most frequently executed queries, up to the maximum specified. You can also limit the scope of SAVE_PLANS to queries only issued after a specified date.

As SAVE_PLANS iterates over past queries, it tests them against various restrictions. In general, directed queries support only SELECT statements as input. Within this broad requirement, input queries are subject to other restrictions. After qualifying all candidate input queries, SAVE_PLANS operates as follows:

Calls CREATE DIRECTED QUERY OPTIMIZER on all qualified input queries, which creates a directed query for each unique input query.
Saves metadata on the new set of directed queries to the system table DIRECTED_QUERIES, where all directed queries of that set share the same integer identifier.

All directed queries created by SAVE_PLANS are initially inactive. You can activate them individually; you can also use SAVE_PLANS_VERSION identifiers to activate, deactivate, and drop one or more sets of directed queries.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

SAVE_PLANS( query-budget [, since-date] [, drop-old-plans[, 'comment']]  )

Arguments

*query-budget*: Maximum number of input queries to save as directed queries, an integer between 1 and 100, inclusive.
since-date: The earliest timestamp of input queries to save as directed queries.
drop-old-plans: Boolean, specifies whether to drop all directed queries generated by earlier SAVE_PLANS invocations. Only directed queries that were generated by the current Vertica version are dropped; directed queries generated by earlier Vertica versions are untouched. To drop older directed queries, use DROP DIRECTED QUERY.
comment: String comment that is attached to all plans saved with this function call.

Privileges

Superuser

Generated metadata

For each set of directed queries that SAVE_PLANS creates, Vertica updates the system table DIRECTED_QUERIES with metadata on each directed query in the set:

Column name	SAVE_PLANS-generated data
QUERY_NAME	Concatenated from the following strings: `save_plans_query-label_query-number_save-plans-version` where: `query-label` is a LABEL hint embedded in the input query associated with this directed query. If theinput query contains no label, then this string is set to `nolabel`. `query-number` is an integer in a continuous sequence between 0 and `budget-query`, which uniquely identifies this directed query from others in the same SAVE_PLANS-generated set. [save-plans-version](/en/sql-reference/system-tables/v-catalog-schema/directed-queries/#SAVE_PLANS_VERSION) identifies the set of directed queries to which this directed query belongs.
SAVE_PLANS_VERSION	Identifies a set of directed queries that were generated by the same call to SAVE_PLANS. All directed queries of the set share the same `SAVE_PLANS_VERSION` integer, which increments by 1 the previous highest `SAVE_PLANS_VERSION` setting. Use this identifier to activate, deactivate, and drop a set of directed queries.
USERNAME	User who invoked SAVE_PLANS to create this set of directed queries.
SINCE_DATE	The `since-date` timestamp supplied to SAVE_PLANS, which specified the earliest timestamp of input queries to evaluate as directed query candidates.
DIGEST	Hash of saved query plan data, used by the optimizer to map identical input queries to the same active directed query.

Examples

See Bulk-Creation of Directed Queries.