IGNORECONST

In a directed query, maps an input query constant to one or more annotated query constants. Vertica also supports :v as an alias of IGNORECONST.

IGNORECONST lets you create directed queries that support input queries with various conditions. IGNORECONST requires an integer argument. This argument matches constants in input and annotated queries that you want the optimizer to ignore.

For details, see Ignoring Constants in Directed Queries.

Syntax

/*+IGNORECONST(arg)*/

/*+:v(arg)*/

Examples

In the following example a directed query is created where input and annotated queries set IGNORECONST hints on employee_city and job_title:

=> SELECT employee_first_name, employee_last_name FROM public.employee_dimension 
     WHERE employee_city='Boston' and job_title ='Cashier';

=> SELECT employee_first_name, employee_last_name FROM public.employee_dimension 
     WHERE employee_city = 'Chicago' and job_title = 'Greeter';

In this case, you can create a directed query where input and annotated queries qualify the settings for employee_city and job_title with IGNORECONST hints:

=> SAVE QUERY SELECT employee_first_name, employee_last_name FROM public.employee_dimension 
   WHERE employee_city='somewhere'/*+IGNORECONST(1)*/
   AND job_title = 'somejob' /*+IGNORECONST(2)*/;
SAVE QUERY

=> CREATE DIRECTED QUERY CUSTOM 'findEmployees' 
   SELECT employee_first_name, employee_last_name FROM public.employee_dimension
   WHERE employee_city='somewhere'/*+IGNORECONST(1)*/ AND job_title ='somejob'/*+IGNORECONST(2)*/;
CREATE DIRECTED QUERY

=> ACTIVATE DIRECTED QUERY findEmployees;
ACTIVATE DIRECTED QUERY

IGNORECONST requires an integer argument. This argument matches constants in input and annotated queries that you want the optimizer to ignore. In the previous example, the input and annotated queries of the directed query, findEmployees, use IGNORECONST to pair two sets of constants:

• IGNORECONST(1) pairs input and annotated query settings for employee_city.
• IGNORECONST(2) pairs input and annotated query settings for job_title.

When the optimizer maps input queries to the directed query findEmployees, the IGNORECONST arguments for employee_city and job_title tell it to ignore the saved values for these two columns. Thus, users can supply any values for these columns.