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 EMP_Dimension WHERE 
Employee_city='Boston' and Employee_position='Cashier';

=> SELECT Employee_first_name, Employee_last_name FROM EMP_Dimension WHERE 
Employee_city='Chicago' and Employee_position='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 EMP_Dimension 
   WHERE Employee_city='somewhere'/*+IGNORECONST(1)*/
   AND Employee_position='somejob' /*+IGNORECONST(2)*/;
SAVE QUERY

=> CREATE DIRECTED QUERY CUSTOM 'findEmployees' 
   SELECT Employee_first_name, Employee_last_name 
   FROM EMP_Dimension /*+projs('public.Emp_Dimension_Unseg')*/ 
   WHERE Employee_city='somewhere'/*+IGNORECONST(1)*/
   AND Employee_position='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:

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 EMP_Dimension WHERE Employee_city='Boston' AND Employee_position='Cashier';

The following active directed query is being executed:
SELECT EMP_Dimension.Employee_first_name, EMP_Dimension.Employee_last_name FROM public.Emp_Dimension EMP_Dimension/*+projs('public.Emp_Dimension_Unseg')*/ WHERE (EMP_Dimension.Employee_city = 'Boston'::varchar(6)) AND (Emp_Dimension.Employee_position = 'Cashier'::varchar(7)))

Access Path:
+-STORAGE ACCESS for EMP_Dimension [Cost: 154, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
|  Projection: public.Emp_Dimension_Unseg
|  Materialize: EMP_Dimension.Employee_first_name, EMP_Dimension.Employee_last_name
|  Filter: (EMP_Dimension.Employee_city = 'Boston')
|  Filter: (Emp_Dimension.Employee_position = 'Cashier')
|  Execute on: Query Initiator

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 EMP_Dimension /*+projs('public.Emp_Dimension_Unseg')*/ 
WHERE Employee_city='anywhere'/*+IGNORECONST(9)*/ AND Employee_position='Greeter';
CREATE DIRECTED QUERY

the optimizer creates the following annotated query:

SELECT /*+ verbatim */ EMP_Dimension.Employee_first_name AS Employee_first_name, EMP_Dimension.Employee_last_name AS Employee_last_name
FROM public.Emp_Dimension AS EMP_Dimension/*+projs('public.Emp_Dimension_Unseg')*/
WHERE (EMP_Dimension.Employee_city = 'anywhere'::varchar(8) /*+IgnoreConst(9)*/) AND (EMP_Dimension.Employee_position = '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