Ignoring Constants in Directed Queries

The IGNORECONST hint lets you create directed queries that support input queries with various conditions. For example, you might want to use the same directed query for the following input queries:

=> 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 Employee_position 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 Employee_position.            
    • 
                                            

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

                                            
For example, the following query plan shows how the optimizer maps one user query to the directed query findEmployees:

                                            
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT employee_first_name, employee_last_name FROM public.employee_dimension WHERE Employee_city='Boston' AND
job_title='Cashier';

 The following active directed query(query name: findEmployees) is being executed:
 SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension WHE
RE ((employee_dimension.employee_city = 'Boston'::varchar(6)) AND (employee_dimension.job_title = 'Cashier'::varchar(7))
)

 Access Path:
 +-STORAGE ACCESS for employee_dimension [Cost: 89, Rows: 10] (PATH ID: 1)
 |  Projection: public.employee_dimension_seg_b0
 |  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: All Nodes
				

                                            

                                            
Embedding IGNORECONST Hints in Optimizer-Generated Directed Queries

                                            
You can embed IGNORECONST hints in the input query argument of CREATE DIRECTED QUERY OPTIMIZER. The optimizer creates an annotated query that includes IGNORECONST hints for the corresponding columns. For example, given this CREATE DIRECTED QUERY OPTIMIZER statement:

                                            
=> CREATE DIRECTED QUERY OPTIMIZER findGreetersAnyCity
    SELECT employee_first_name, employee_last_name
    FROM public.employee_dimension WHERE employee_city='anywhere'/*+IGNORECONST(9)*/ AND job_title='Greeter';
CREATE DIRECTED QUERY

                                            

                                            
the optimizer creates the following annotated query:

                                            
SELECT annotated_query FROM v_catalog.directed_queries WHERE query_name='findGreetersAnyCity';
-[ RECORD 1 ]---+------------------------------------------------------------
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_seg')*/
WHERE (employee_dimension.employee_city = 'anywhere'::varchar(8) /*+IgnoreConst(9)*/) AND (employee_dimension.job_title = 'Greeter'::varchar(7))

                                            

                                            
Mapping One-to-Many IGNORECONST Hints

                                            
The examples shown so far demonstrate one-to-one pairings of IGNORECONST hints. You can also use IGNORECONST to map one input constant to multiple constants in an annotated query. This approach can be especially useful when you want to provide the optimizer with explicit instructions how to execute a query that joins tables.

                                            
For example, this simple query joins two tables:

                                            
SELECT * FROM S JOIN T ON S.a = T.b WHERE S.a = 8;

                                            

                                            
In this case, the optimizer can infer that S.a and T.b have the same value and implements the join accordingly. In other cases, you might want to provide the optimizer explicit guidance on implementing a join condition. Given the previous input query, you can create a directed query that uses the IGNORECONST hint to map query input for S.a to S.a and T.b:

                                            
=> SAVE QUERY SELECT * FROM S JOIN T ON S.a = T.b WHERE S.a = 999/*+IGNORECONST(1)*/;
SAVE QUERY

=> CREATE DIRECTED QUERY CUSTOM joinST SELECT * FROM S JOIN T ON S.a = T.b 
WHERE S.a = 999/*+IGNORECONST(1)*/ AND T.b = 999/*+IGNORECONST(1)*/;
CREATE DIRECTED QUERY

=> ACTIVATE DIRECTED QUERY joinST;
ACTIVATE DIRECTED QUERY
				

                                            

                                            
Now, given the following input query:

                                            
SELECT * FROM S JOIN T ON S.a = T.b WHERE S.a = 3;

                                            

                                            
the optimizer uses the directed query joinST and its IGNORECONST hints to rewrite the query as follows:

                                            
QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT * FROM S JOIN T ON S.a = T.b WHERE S.a = 3;

The following active directed query(query name: joinST) is being executed:
SELECT S.a, T.b FROM (public.S JOIN public.T ON ((S.a = T.b))) WHERE ((S.a = 3) AND (T.b = 3))

Access Path:
+-JOIN MERGEJOIN(inputs presorted) [Cost: 11, Rows: 9 (NO STATISTICS)] (PATH ID: 1)
|  Join Cond: (S.a = T.b)
|  Execute on: v_vmart_node0002
| +-- Outer -> STORAGE ACCESS for S [Cost: 5, Rows: 9 (NO STATISTICS)] (PATH ID: 2)
| |      Projection: public.S_b0
| |      Materialize: S.a
| |      Filter: (S.a = 3)
| |      Execute on: v_vmart_node0002
| |      Runtime Filter: (SIP1(MergeJoin): S.a)
| +-- Inner -> STORAGE ACCESS for T [Cost: 5, Rows: 9 (NO STATISTICS)] (PATH ID: 3)
| |      Projection: public.T_b0
| |      Materialize: T.b
| |      Filter: (T.b = 3)
| |      Execute on: v_vmart_node0002