这是本节的多页打印视图。点击此处打印.

管理定向查询

1: 获取定向查询
2: 识别处于活动状态的定向查询
3: 激活和停用定向查询
4: 从编录中导出定向查询
5: 删除定向查询

Vertica 提供了许多管理定向查询的方法：

1 - 获取定向查询

可按以下两种方式获取有关定向查询的编录信息：

使用 GET DIRECTED QUERY。
查询系统表 DIRECTED_QUERIES。

GET DIRECTED QUERY

GET DIRECTED QUERY 在指定输入查询上查询系统表 DIRECTED_QUERIES。它返回映射到输入查询的所有定向查询的详细信息。

以下 GET DIRECTED QUERY 语句返回映射到以下输入查询 findEmployeesCityJobTitle_OPT 的定向查询：

=> GET DIRECTED QUERY SELECT employee_first_name, employee_last_name from employee_dimension where employee_city='Boston' AND job_title='Cashier' order by employee_last_name, employee_first_name;
-[ RECORD 1 ]---+
query_name      | findEmployeesCityJobTitle_OPT
is_active       | t
vertica_version | Vertica Analytic Database v11.0.1-20210815
comment         | Optimizer-generated directed query
creation_date   | 2021-08-20 14:53:42.323963
annotated_query | 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) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/)) ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name

DIRECTED_QUERIES

可以直接查询系统表 DIRECTED_QUERIES。例如：

=> SELECT query_name, is_active FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name ILIKE '%findEmployeesCityJobTitle%';
          query_name           | is_active
-------------------------------+-----------
 findEmployeesCityJobTitle_OPT | t
(1 row)

当心

字段 INPUT_QUERY 和 ANNOTATED_QUERY 的查询结果在 ~32K 个字符之后会被截断。可以使用两种方法获取这两个字段的完整内容：

使用语句 GET DIRECTED QUERY。
使用 EXPORT_CATALOG 导出定向查询。

2 - 识别处于活动状态的定向查询

多个定向查询可以映射到同一输入查询。系统表 DIRECTED_QUERIES 中的 is_active 列标识处于活动状态的定向查询。如果多个定向查询对于同一输入查询均处于活动状态，优化器则使用要创建的第一个定向查询。在这种情况下，可使用 EXPLAIN 识别哪个定向查询处于活动状态。

提示

对于给定输入查询，一次仅激活一个定向查询是非常好的做法。

DIRECTED_QUERIES 上的以下查询查找所有处于活动状态的定向查询，且其中的输入查询包含被查询表 employee_dimension 的名称：

=> SELECT * FROM directed_queries WHERE input_query ILIKE ('%employee_dimension%') AND is_active='t';
-[ RECORD 1 ]------+
query_name         | findEmployeesCityJobTitle_OPT
is_active          | t
vertica_version    | Vertica Analytic Database v11.0.1-20210815
comment            | Optimizer-generated directed query
save_plans_version | 0
username           | dbadmin
creation_date      | 2021-08-20 14:53:42.323963
since_date         |
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, employee_dimension.employee_last_name FROM public.employee_dimension 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 employee_dimension.employee_last_name, employee_dimension.employee_first_name

如果对输入查询运行 EXPLAIN，它将返回一个确认使用 findEmployeesCityJobTitle_OPT 作为活动定向查询的查询计划：

=> EXPLAIN SELECT employee_first_name, employee_last_name FROM employee_dimension WHERE employee_city='Boston' AND job_title ='Cashier' 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 = 'Boston'::varchar(6)) AND (employee_dimension.job_title = 'Cashier'::varchar(7))) ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name

3 - 激活和停用定向查询

优化器仅使用处于活动状态的定向查询。如果多个定向查询共享同一输入查询，优化器将使用要创建的第一个查询。

定向查询分别使用 ACTIVATE DIRECTED QUERY 和 DEACTIVATE DIRECTED QUERY 来激活和停用。例如，以下 ACTIVATE DIRECTED QUERY 语句停用 RegionalSalesProducts_JoinTables 并激活 RegionalSalesProduct：


=> DEACTIVATE DIRECTED QUERY RegionalSalesProducts_JoinTables;
DEACTIVATE DIRECTED QUERY;
=> ACTIVATE DIRECTED QUERY RegionalSalesProduct;
ACTIVATE DIRECTED QUERY;

Vertica 为所有会话中的给定查询使用处于活动状态的定向查询，除非它被 DEACTIVATE DIRECTED QUERY 明确停用或被 DROP DIRECTED QUERY 从存储空间中移除。如果在数据库关闭时定向查询处于活动状态，当您重新启动数据库时，Vertica 会自动将其重新激活。

定向查询停用后，查询优化器通过使用另一个定向查询（如果存在这样的定向查询）处理输入查询的后续调用。否则，它会生成自己的查询计划。

4 - 从编录中导出定向查询

提示

也可以将查询计划作为定向查询导出到外部 SQL 文件。请参阅批量导出查询计划。

升级到新版本 Vertica 之前，您可以导出定向查询，从而将其用于实现最佳性能对系统而言至关重要的查询。

配合使用 EXPORT_CATALOG 和实参 DIRECTED_QUERIES 从数据库编录导出所有当前定向查询及其当前激活状态：

=> SELECT EXPORT_CATALOG('../../export_directedqueries', 'DIRECTED_QUERIES');
EXPORT_CATALOG
-------------------------------------
Catalog data exported successfully

EXPORT_CATALOG 创建一个脚本以重新创建定向查询，如以下示例所示：

SAVE 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;
CREATE DIRECTED QUERY CUSTOM findEmployeesCityJobTitle_OPT COMMENT 'Optimizer-generated directed query' OPTVER 'Vertica Analytic Database v11.0.1-20210815' PSDATE '2021-08-20 14:53:42.323963' 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) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/))
ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name;
ACTIVATE DIRECTED QUERY findEmployeesCityJobTitle_OPT;

注意

EXPORT_CATALOG 创建的脚本指定使用 CREATE DIRECTED QUERY CUSTOM 重新创建所有定向查询，而不管其最初是如何创建的。

升级完成后，使用 DROP DIRECTED QUERY 从数据库编录中移除每个定向查询。或者，编辑导出脚本并在每个 CREATE DIRECTED QUERY 语句之前插入 DROP DIRECTED QUERY 语句。例如，您可以使用粗体显示的更改来修改先前生成的脚本：
```
SAVE QUERY SELECT employee_dimension.employee_first_name, ... ;
DROP DIRECTED QUERY findEmployeesCityJobTitle_OPT;
CREATE DIRECTED QUERY CUSTOM findEmployeesCityJobTitle_OPT COMMENT 'Optimizer-generated ... ;
ACTIVATE DIRECTED QUERY findEmployeesCityJobTitle_OPT;
```

当您运行此脚本时，Vertica 将重新创建定向查询并还原其激活状态：

=> \i /home/dbadmin/export_directedqueries
SAVE QUERY
DROP DIRECTED QUERY
CREATE DIRECTED QUERY
ACTIVATE DIRECTED QUERY

5 - 删除定向查询

DROP DIRECTED QUERY 从数据库编录中移除指定的定向查询。如果该定向查询处于活动状态，Vertica 会在移除前将其停用。

例如：

=> DROP DIRECTED QUERY findBostonCashiers_CUSTOM;
DROP DIRECTED QUERY