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,
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
=> 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