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