Custom directed queries

CREATE DIRECTED QUERY CUSTOM specifies an annotated query and pairs it to an input query previously saved by SAVE QUERY. You must issue both statements in the same user session.

For example, you might want a query to use a specific projection:

1. Specify the query with SAVE QUERY:

   => SAVE QUERY SELECT employee_first_name, employee_last_name FROM employee_dimension
       WHERE employee_city='Boston' AND job_title='Cashier';
   SAVE QUERY
   

   Note

   The input query that you supply to SAVE QUERY only supports the :v hint.

2. Create a custom directed query with CREATE DIRECTED QUERY CUSTOM, which specifies an annotated query and associates it with the saved query. The annotated query includes a /*+projs*/ hint, which instructs the optimizer to use the projection public.emp_dimension_unseg when users call the saved 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
   

   Caution

   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 statements so the saved and directed queries are correctly matched.

3. Activate the directed query:

   => ACTIVATE DIRECTED QUERY findBostonCashiers_CUSTOM;
   ACTIVATE DIRECTED QUERY
   

4. After activation, the optimizer uses this directed query 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:

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

See also

Rewriting Join Queries