Directed queries functions

• 1: CLEAR_DIRECTED_QUERY_USAGE
• 2: EXPORT_DIRECTED_QUERIES
• 3: IMPORT_DIRECTED_QUERIES
• 4: SAVE_PLANS

1 - CLEAR_DIRECTED_QUERY_USAGE

Resets the counter in the DIRECTED_QUERY_STATUS table for a single query or for all queries.

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

Behavior type

Syntax

CLEAR_DIRECTED_QUERY_USAGE( [ 'query-name' ] )

Arguments

query-name

The name of the directed query to reset. If omitted, the function resets counters for all queries.

Privileges

Examples

In the following example, three directed queries have been used:

=> SELECT query_name, sum(hits) FROM DIRECTED_QUERY_STATUS GROUP BY 1;
          query_name           | sum 
-------------------------------+-----
 findEmployeesCityJobTitle_OPT | 5
 save_plans_nolabel_3_3        | 2
 save_plans_nolabel_6_3        | 3
(3 rows)

After calling the function for one of the queries, its counter is reset to 0:

=> SELECT CLEAR_DIRECTED_QUERY_USAGE('findEmployeesCityJobTitle_OPT');
 CLEAR_DIRECTED_QUERY_USAGE 
----------------------------
 Usage cleared.
(1 row)

=> SELECT query_name, sum(hits) FROM DIRECTED_QUERY_STATUS GROUP BY 1;
          query_name           | sum 
-------------------------------+-----
 findEmployeesCityJobTitle_OPT | 0
 save_plans_nolabel_3_3        | 2
 save_plans_nolabel_6_3        | 3
(3 rows)

2 - EXPORT_DIRECTED_QUERIES

Generates SQL for creating directed queries from a set of input queries.

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

Behavior type

Syntax

EXPORT_DIRECTED_QUERIES('input-file', '[output-file]')

Arguments

input-file

A SQL file that contains one or more input queries. See Input Format below for details on format requirements.

output-file

Specifies where to write the generated SQL for creating directed queries. If output-file already exists, EXPORT_DIRECTED_QUERIES returns with an error. If you supply an empty string, Vertica writes the SQL to standard output. See Output Format below for details.

Privileges

Input format

The input file that you supply to EXPORT_DIRECTED_QUERIES contains one or more input queries. For each input query, you can optionally specify two fields that are used in the generated directed query:

• DirQueryName provides the directed query's unique identifier, a string that conforms to conventions described in Identifiers.

• DirQueryComment specifies a quote-delimited string, up to 128 characters.

You format each input query as follows:

--DirQueryName=query-name
--DirQueryComment='comment'
input-query

Output format

EXPORT_DIRECTED_QUERIES generates SQL for creating directed queries, and writes the SQL to the specified file or to standard output. In both cases, output conforms to the following format:

/* Query: directed-query-name */
/* Comment: directed-query-comment */
SAVE QUERY input-query;
CREATE DIRECTED QUERY CUSTOM 'directed-query-name'
COMMENT 'directed-query-comment'
OPTVER 'vertica-release-num'
PSDATE 'timestamp'
annotated-query

If a given input query omits DirQueryName and DirQueryComment fields, EXPORT_DIRECTED_QUERIES automatically generates the following output:

• /* Query: Autoname:timestamp.n */, where n is a zero-based integer index that ensures uniqueness among auto-generated names with the same timestamp.

• /* Comment: Optimizer-generated directed query */

Error handling

If any errors or warnings occur during EXPORT_DIRECTED_QUERIES execution, it returns with a message like this one:

1 queries successfully exported.
1 warning message was generated.
Queries exported to /home/dbadmin/outputQueries.
See error report, /home/dbadmin/outputQueries.err for details.

EXPORT_DIRECTED_QUERIES writes all errors and warnings to a file that it creates on the same path as the output file, and uses the output file's base name.

For example:

---------------------------------------------------------------------------------------------------
WARNING: Name field not supplied. Using auto-generated name: 'Autoname:2016-04-25 15:03:32.115317.0'
Input Query: SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.job_title FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) ORDER BY employee_dimension.job_title;
END WARNING

Examples

See Exporting directed queries.

See also

• Batch query plan export
• IMPORT_DIRECTED_QUERIES

3 - IMPORT_DIRECTED_QUERIES

Imports to the database catalog directed queries from a SQL file that was generated by EXPORT_DIRECTED_QUERIES. If no directed queries are specified, Vertica lists all directed queries in the SQL file.

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

Behavior type

Syntax

IMPORT_DIRECTED_QUERIES( 'export-file'[, 'directed-query-name'[,...] ] )

Arguments

export-file

A SQL file generated by EXPORT_DIRECTED_QUERIES. When you run this file, Vertica creates the specified directed queries in the current database catalog.

directed-query-name

The name of a directed query that is defined in export-file. You can specify multiple comma-delimited directed query names.

If you omit this parameter, Vertica lists the names of all directed queries in export-file.

Privileges

Examples

See Importing directed queries.

See also

4 - SAVE_PLANS

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:

1. Calls CREATE DIRECTED QUERY OPTIMIZER on all qualified input queries, which creates a directed query for each unique input query.
2. 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

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

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:

save_plans_query-label_query-number_save-plans-version

Examples

See Bulk-Creation of Directed Queries.