从编录中导出定向查询

提示

也可以将查询计划作为定向查询导出到外部 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