Getting Directed Queries

You can obtain catalog information about directed queries in two ways:

Run GET DIRECTED QUERY

GET DIRECTED QUERY queries the system table V_CATALOG.DIRECTED_QUERIES on the specified input query. It returns a list of directed queries that map to the input query.

The following GET DIRECTED QUERY statement returns two directed queries that map to the same input query, findBostonCashiers_OPT and findBostonCashiers_CUSTOM:

=> GET DIRECTED QUERY SELECT employee_first_name, employee_last_name 
    FROM employee_dimension WHERE employee_city='Boston' AND job_title='Cashier';
-[ RECORD 1 ]---+
query_name      | findBostonCashiers_OPT
is_active       | f
vertica_version | Vertica Analytic Database v7.2.3
comment         | Optimizer-generated directed query
creation_date   | 2016-04-25 08:17:26.913339
annotated_query | SELECT /*+ verbatim */ employee_dimension.employee_first_name AS employee_first_name, employee_dimension.employee_last_name AS employee_last_name
FROM public.employee_dimension AS employee_dimension/*+projs('public.employee_dimension')*/
WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) AND (employee_dimension.job_title = 'Cashier'::varchar(7))
-[ RECORD 2 ]---+
query_name      | findBostonCashiers_CUSTOM
is_active       | t
vertica_version | Vertica Analytic Database v7.2.3
comment         | Custom directed query
creation_date   | 2016-04-25 09:15:11.464417
annotated_query | 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)))

Query V_CATALOG.DIRECTED_QUERIES

You can query the system table V_CATALOG.DIRECTED_QUERIES directly. For example:

=> SELECT query_name, is_active FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name ILIKE '%findBostonCashiers%';
        query_name         | is_active
---------------------------+-----------
 findBostonCashiers_CUSTOM | t
 findBostonCashiers_OPT    | f
(2 rows)

(2 rows)


Query results for the fields INPUT_QUERY and INPUT_QUERY are truncated after ~32K characters. You can get the full content of both fields in two ways: