Custom Directed Queries
CREATE DIRECTED QUERY CUSTOM
specifies an annotated query and pairs it to an input query previously saved by SAVE QUERY
. The SAVE QUERY
statement must precede CREATE DIRECTED QUERY CUSTOM
. SAVE QUERY
temporarily saves an input query for use in creating a directed query. You must issue both statements in the same user session.
The input query that you supply to SAVE QUERY
supports only one optimizer hint, IGNORECONST
.
In the following example, SAVE QUERY
saves a query. The CREATE DIRECTED QUERY CUSTOM
statement that follows it provides an annotated query that includes a /*+projs*/
hint. This hint instructs the optimizer to use the projection public.Emp_Dimension_Unseg
:
=> SAVE QUERY SELECT employee_first_name, employee_last_name FROM employee_dimension WHERE employee_city='Boston' AND job_title='Cashier'; SAVE QUERY => CREATE DIRECTED QUERY CUSTOM 'findBostonCashiers_CUSTOM' SELECT employee_first_name, employee_last_name FROM employee_dimension /*+Projs('public.emp_dimension_unseg')*/ WHERE employee_city='Boston' AND job_title='Cashier'; CREATE DIRECTED QUERY
Vertica associates a saved query and annotated query without checking whether the input and annotated queries are compatible. Be careful to sequence SAVE QUERY
and CREATE DIRECTED QUERY CUSTOM
so the saved and directed queries are correctly matched.
After this directed query plan is activated, the optimizer uses it to generate a query plan for all subsequent invocations of its input query. The following EXPLAIN
output verifies the optimizer's use of this directed query and the projection it specifies:
=> DEACTIVATE DIRECTED QUERY findBostonCashiers_OPT; DEACTIVATE DIRECTED QUERY => ACTIVATE DIRECTED QUERY findBostonCashiers_CUSTOM; ACTIVATE DIRECTED QUERY => EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension WHERE employee_city='Boston' AND job_title='Cashier'; QUERY PLAN ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension where employee_city='Boston' AND job_title='Cashier'; The following active directed query(query name: findBostonCashiers_CUSTOM) is being executed: SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension/*+Projs('public.Emp_Dimension_Unseg')*/ WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6)) AND (employee_dimension.job_title = 'Cashier'::varchar(7))) Access Path: +-STORAGE ACCESS for employee_dimension [Cost: 158, Rows: 10K (NO STATISTICS)] (PATH ID: 1) | Projection: public.emp_dimension_unseg | Materialize: employee_dimension.employee_first_name, employee_dimension.employee_last_name | Filter: (employee_dimension.employee_city = 'Boston') | Filter: (employee_dimension.job_title = 'Cashier') | Execute on: Query Initiator