优化器生成的定向查询

CREATE DIRECTED QUERY OPTIMIZER 将输入查询传递给优化器，优化器从自己的查询计划生成带注释的查询。然后，它将输入查询和带注释的查询配对并将其保存为定向查询。此定向查询可用于处理其他除了作为查询结果筛选依据的谓词字符串以外完全相同的查询。

可在升级之前使用优化器生成的定向查询来捕获查询计划。如果在升级后检测到给定查询的性能下降，这样做将会非常有用。在这种情况下，可使用相应的定向查询重新创建早期查询计划，并将其性能与当前优化器生成的计划相对比。

示例

以下 SQL 语句创建和激活定向查询 findEmployeesCityJobTitle_OPT：


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

=> ACTIVATE DIRECTED QUERY findEmployeesCityJobTitle_OPT;
ACTIVATE DIRECTED QUERY

在激活此定向查询计划后，优化器会使用它为此输入查询的所有后续调用以及其他类似调用生成查询计划。可通过调用 GET DIRECTED QUERY 或查询系统表 DIRECTED_QUERIES 来查看优化器生成的带注释查询：

=> SELECT input_query, annotated_query FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name = 'findEmployeesCityJobTitle_OPT';
-[ RECORD 1 ]---+----------------------------------------------------------------------------
input_query     | SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension
WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/))
ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name
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) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/)
ORDER BY 2 ASC, 1 ASC

带注释的查询包括以下提示：

/*+verbatim*/ 指定完全按编写执行带注释的查询并相应地生成查询计划。
/*+projs('public.Emp_Dimension')*/ 指示优化器创建使用投影 public.Emp_Dimension 的查询计划。
/*+:v({{< codevar >}}n{{< /codevar >}})*/ (alias of /*+IGNORECONST(n)*/) 多次包含在带注释查询和输入查询中。这些提示限定查询谓词中的两个常量： Boston 和 Cashier. Each :v 提示有一个整数实参 n，该实参将输入查询和带注释查询中的对应常量进行配对： *+:v(1)*/ （用于 Boston）和 /*+:v(2)*/（用于 Cashier）。这些提示告诉优化器在决定是否将此定向查询应用于其他类似的输入查询时忽略这些常量。因此，忽略常量提示可让您对不同的输入查询使用相同的定向查询。

以下查询对 employee_city 和 job_title 列使用不同的值，但在其他方面与定向查询 EmployeesCityJobTitle_OPT 的原始输入查询相同：

=> SELECT employee_first_name, employee_last_name FROM public.employee_dimension
     WHERE employee_city = 'San Francisco' and job_title = 'Branch Manager' ORDER BY employee_last_name, employee_first_name;

如果定向查询 EmployeesCityJobTitle_OPT 处于活动状态，则优化器可以将其用于此查询：

=> EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension WHERE employee_city='San Francisco' AND job_title='Branch Manager' ORDER BY employee_last_name, employee_first_name;
 ...
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------
 EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension WHERE employee_city='San Francisco' AND job_title='Branch Manager' ORDER BY employee_last_name, employee_first_name;

 The following active directed query(query name: findEmployeesCityJobTitle_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 = 'San Francisco'::varchar(13)) AND (employee_dimension.job_title = 'Branch Manager'::varchar(14)))
 ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name

 Access Path:
 +-SORT [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Order: employee_dimension.employee_last_name ASC, employee_dimension.employee_first_name ASC
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for employee_dimension [Cost: 60, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: public.employee_dimension_super
 | |      Materialize: employee_dimension.employee_first_name, employee_dimension.employee_last_name
 | |      Filter: (employee_dimension.employee_city = 'San Francisco')
 | |      Filter: (employee_dimension.job_title = 'Branch Manager')
 | |      Execute on: All Nodes
 ...