在升级到新 Vertica 版本之前，您可能希望使用定向查询来保存查询计划，以便可能在新数据库中重新使用。您无法预测哪些查询计划可能会重新使用，因此可能要保存很多（或全部）数据库查询的查询计划。但是，每天运行的查询有数百个。通过重复调用 CREATE DIRECTED QUERY 将每个查询计划保存到数据库编录是不切实际的。此外，这样做会显著增加编录大小，可能还会影响性能。
在此情况下，可绕过数据库编录并将查询计划作为定向查询批量导出到外部 SQL 文件。通过为查询计划存储卸载，可以保存当前数据库中任意数量的查询计划，而不影响编录大小和性能。升级之后，您可以决定在新数据库中保留哪些查询计划，并有选择地导入相应的定向查询。
Vertica 提供一组支持此方法的元函数：
EXPORT_DIRECTED_QUERIES 从一组输入查询生成查询计划，并编写用于创建封装这些计划的定向查询的 SQL。
IMPORT_DIRECTED_QUERIES 将定向查询从由 EXPORT_DIRECTED_QUERIES 生成的 SQL 文件导入数据库编录中。
可以将任意数量的查询计划作为定向查询批量导出到外部 SQL 文件中，如下所示：
创建一个 SQL 文件，其中包含要保存其查询计划的输入查询。请参阅下文的输入格式。
对该 SQL 文件调用元函数 EXPORT_DIRECTED_QUERIES。此元函数具有两个实参：
例如，以下 EXPORT_DIRECTED_QUERIES 语句指定了输入文件 inputQueries 和输出文件 outputQueries：
=> SELECT EXPORT_DIRECTED_QUERIES('/home/dbadmin/inputQueries','/home/dbadmin/outputQueries');
EXPORT_DIRECTED_QUERIES
---------------------------------------------------------------------------------------------
1 queries successfully exported.
Queries exported to /home/dbadmin/outputQueries.
(1 row)
您提供给 EXPORT_DIRECTED_QUERIES 的输入文件包含一个或多个输入查询。对于每个输入查询，您可以选择指定两个字段，这两个字段将用于生成的定向查询中：
DirQueryName 提供了定向查询的唯一标识符，即一个符合标识符中所述惯例的字符串。
DirQueryComment 指定了一个最多 128 个字符并以引号分隔的字符串。
您可以按如下方式设置每个输入查询的格式：
--DirQueryName=query-name
--DirQueryComment='comment'
input-query
例如，一个文件可以指定一个输入查询，如下所示：
/* Query: findEmployeesCityJobTitle_OPT */
/* Comment: This query finds all employees of a given city and job title, ordered by employee name */
SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.job_title FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) ORDER BY employee_dimension.job_title;
EXPORT_DIRECTED_QUERIES 会将生成用于创建定向查询的 SQL，并将此 SQL 写入到指定文件或标准输出中。对于这两种情况，输出都遵循以下格式：
/* Query: directed-query-name */
/* Comment: directed-query-comment */
SAVE QUERY input-query;
CREATE DIRECTED QUERY CUSTOM 'directed-query-name'
COMMENT 'directed-query-comment'
OPTVER 'vertica-release-num'
PSDATE 'timestamp'
annotated-query
例如，给定前面的输入时，Vertica 会将以下输出写入 /home/dbadmin/outputQueries：
/* Query: findEmployeesCityJobTitle_OPT */
/* Comment: This query finds all employees of a given city and job title, ordered by employee name */
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 'This query finds all employees of a given city and job title, ordered by employee name'
OPTVER 'Vertica Analytic Database v11.1.0-20220102'
PSDATE '2022-01-06 13:45:17.430254'
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;
如果给定的输入查询省略了 DirQueryName 和 DirQueryComment 字段，则 EXPORT_DIRECTED_QUERIES 会自动生成以下输出：
/* Query: Autoname:时间戳
.n */，其中 n 为基于零的整数索引，用于确保具有相同时间戳的自动生成名称具有唯一性。
/* Comment: Optimizer-generated directed query */
例如，以下输入文件包含一个 SELECT 语句，并且省略 DirQueryName 和 DirQueryComment 字段：
SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name
FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6))
ORDER BY employee_dimension.job_title;
给定这个文件，EXPORT_DIRECTED_QUERIES 会返回一个关于缺少输入字段的警告，它还会将该警告写入错误文件：
> SELECT EXPORT_DIRECTED_QUERIES('/home/dbadmin/inputQueries2','/home/dbadmin/outputQueries3');
EXPORT_DIRECTED_QUERIES
--------------------------------------------------------------------------------------------------------------
1 queries successfully exported.
1 warning message was generated.
Queries exported to /home/dbadmin/outputQueries3.
See error report, /home/dbadmin/outputQueries3.err for details.
(1 row)
输出文件包含以下内容：
/* Query: Autoname:2022-01-06 14:11:23.071559.0 */
/* Comment: Optimizer-generated directed query */
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)*/) ORDER BY employee_dimension.job_title;
CREATE DIRECTED QUERY CUSTOM 'Autoname:2022-01-06 14:11:23.071559.0'
COMMENT 'Optimizer-generated directed query'
OPTVER 'Vertica Analytic Database v11.1.0-20220102'
PSDATE '2022-01-06 14:11:23.071559'
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)*/)
ORDER BY employee_dimension.job_title ASC;
如果在执行 EXPORT_DIRECTED_QUERIES 期间发生任何错误或警告，它会返回一条类似于以下内容的消息：
1 queries successfully exported.
1 warning message was generated.
Queries exported to /home/dbadmin/outputQueries.
See error report, /home/dbadmin/outputQueries.err for details.
EXPORT_DIRECTED_QUERIES 会将所有错误或警告都写入到一个在与输出文件相同的路径下所创建的文件中，并使用输出文件的基名。
在上一示例中，输出文件名为 /home/dbadmin/outputQueries，因此 EXPORT_DIRECTED_QUERIES 会将错误写入 /home/dbadmin/outputQueries.err。
错误文件可以捕获许多错误和警告，例如，可以捕获 EXPORT_DIRECTED_QUERIES 无法创建定向查询的所有实例。在以下示例中，错误文件包含一个警告，指示没有为指定的输入查询提供名称字段，并且还记录了自动为该查询生成的名称：
----------------------------------------------------------------------------------------------------
WARNING: Name field not supplied. Using auto-generated name: 'Autoname:2016-10-13 09:44:33.527548.0'
Input Query: SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.job_title FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) ORDER BY employee_dimension.job_title;
END WARNING
确定您希望在当前数据库中使用的已导出查询计划后，使用 IMPORT_DIRECTED_QUERIES 将其导入。您为此函数提供您使用 EXPORT_DIRECTED_QUERIES 创建的导出文件的名称以及您希望导入的定向查询的名称。例如：
=> SELECT IMPORT_DIRECTED_QUERIES('/home/dbadmin/outputQueries','FindEmployeesBoston');
IMPORT_DIRECTED_QUERIES
------------------------------------------------------------------------------------------
1 directed queries successfully imported.
To activate a query named 'my_query1':
=> ACTIVATE DIRECTED QUERY 'my_query1';
(1 row)
导入所需的定向查询后，必须使用 ACTIVATE DIRECTED QUERY 将其激活，然后才能使用它们创建查询计划。