自定义定向查询

CREATE DIRECTED QUERY CUSTOM 指定带注释的查询并将其与 SAVE QUERY 先前保存的输入查询配对。必须在同一用户会话中发出这两个语句。

例如，您可能希望某查询使用特定投影：

使用 SAVE QUERY 指定查询：

=> SAVE QUERY SELECT employee_first_name, employee_last_name FROM employee_dimension
    WHERE employee_city='Boston' AND job_title='Cashier';
SAVE QUERY

注意

您提供给 SAVE QUERY 的输入查询仅支持 :v 提示。

使用 CREATE DIRECTED QUERY CUSTOM 创建自定义定向查询，它指定带注释的查询并将其与保存的查询相关联。带注释的查询包含一个 /*+projs*/ 提示，它指示优化器在用户调用保存的查询时使用投影 public.emp_dimension_unseg：
```
=> CREATE DIRECTED QUERY CUSTOM 'findBostonCashiers_CUSTOM'
   SELECT employee_first_name, employee_last_name
   FROM employee_dimension /*+Projs('public.emp_dimension_unseg')*/
   WHERE employee_city='Boston' AND job_title='Cashier';
CREATE DIRECTED QUERY
```
当心
Vertica 将保存的查询与带注释的查询相关联，而不检查输入查询和带注释的查询是否兼容。请小心安排 SAVE QUERY 和 CREATE DIRECTED QUERY CUSTOM 语句的顺序，以使保存的查询和定向查询正确匹配。

激活此定向查询：

=> ACTIVATE DIRECTED QUERY findBostonCashiers_CUSTOM;
ACTIVATE DIRECTED QUERY

激活后，优化器将使用此定向查询为其输入查询的所有后续调用生成查询计划。下面的 EXPLAIN 输出验证优化器使用此定向查询的情况及其指定的投影：


=> EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension
   WHERE employee_city='Boston' AND job_title='Cashier';

QUERY PLAN
------------------------------
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_CUSTOM) is being executed:
 SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name
  FROM public.employee_dimension/*+Projs('public.emp_dimension_unseg')*/
  WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6)) AND (employee_dimension.job_title = 'Cashier'::varchar(7)))

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

另请参阅

重写联接查询