Optimizer-Generated Directed Queries

CREATE DIRECTED QUERY OPTIMIZER passes an input query to the optimizer, which generates an annotated query from its own query plan. It then pairs the input and annotated queries and saves them as a directed query.

The input query that you supply for optimizer-generated directed queries supports only one optimizer hint, IGNORECONST.

You can use optimized-generated directed queries to capture query plans before you upgrade. Doing so can be especially useful if you detect diminished performance of a given query after the upgrade. In this case, you can use the corresponding directed query to recreate an earlier query plan, and compare its performance to the plan generated by the current optimizer.

For example, the following SQL statements create and activate the directed query findBostonCashiers_OPT:

=> CREATE DIRECTED QUERY OPTIMIZER 'findBostonCashiers_OPT' 
   SELECT employee_first_name, employee_last_name FROM public.employee_dimension 
   	WHERE employee_city='Boston' and job_title='Cashier';
CREATE DIRECTED QUERY

=> ACTIVATE DIRECTED QUERY findBostonCashiers_OPT;
ACTIVATE DIRECTED QUERY

After this directed query plan is activated, the optimizer uses it to generate a query plan for all subsequent invocations of its input query. You can view the optimizer-generated annotated query by either calling GET DIRECTED QUERY or querying the system table V_CATALOG.DIRECTED_QUERIES:

=> SELECT query_name, annotated_query FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name = 'findBostonCashiers_OPT';
-[ RECORD 1 ]---+----------------------------------------------------------------------------
query_name      | findBostonCashiers_OPT
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)

The annotated SQL includes two hints:

  • /*+verbatim*/ specifies to execute the annotated query exactly as written and produce a query plan accordingly.
  • /*+projs('public.Emp_Dimension')*/ directs the optimizer to create a query plan that uses the projection public.Emp_Dimension.

The following EXPLAIN statement verifies the optimizer's use of this directed query and the specified projection:

 QUERY PLAN DESCRIPTION:
 ------------------------------
 EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension WHERE employee_city='Boston' AND job_title='Cashier';

 The following active directed query(query name: findBostonCashiers_OPT) is being executed:
 SELECT /*+verbatim*/ employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension employee_dimension/*+projs('public.employee_dimension')*/ WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6)) AND (employee_dimension.job_title = 'Cashier'::varchar(7)))

 Access Path:
 +-STORAGE ACCESS for employee_dimension [Cost: 60, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.employee_dimension_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