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 details of all 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: