Exporting Directed Queries from the Catalog
You can also export query plans as directed queries to an external SQL file. See Batch Query Plan Export.
Before upgrading to a new version of Vertica, you can export directed queries for those queries whose optimal performance is critical to your system:
- Use
EXPORT_CATALOGwith the argumentDIRECTED_QUERIESto export from the database catalog all current directed queries and their current activation status:=> SELECT EXPORT_CATALOG('../../export_directedqueries', 'DIRECTED_QUERIES'); EXPORT_CATALOG ------------------------------------- Catalog data exported successfully EXPORT_CATALOGcreates a script to recreate the directed queries, as in the following example: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)) AND (employee_dimension.job_title = 'Cashier'::varchar(7))); CREATE DIRECTED QUERY CUSTOM findBostonCashiers_OPT COMMENT 'Optimizer-generated directed query' OPTVER 'Vertica Analytic Database v7.2.3-20160425' PSDATE '2016-04-25 08:17:26.913339' 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)) AND (employee_dimension.job_title = 'Cashier'::varchar(7)); DEACTIVATE DIRECTED QUERY findBostonCashiers_OPT; 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)) AND (employee_dimension.job_title = 'Cashier'::varchar(7))); CREATE DIRECTED QUERY CUSTOM findBostonCashiers_CUSTOM COMMENT 'Custom directed query' OPTVER 'Vertica Analytic Database v7.2.3-20160425' PSDATE '2016-04-25 09:15:11.464417' 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))); ACTIVATE DIRECTED QUERY findBostonCashiers_CUSTOM;- After the upgrade is complete, remove each directed query from the database catalog with
DROP DIRECTED QUERY. Alternatively, edit the export script and insert aDROP DIRECTED QUERYstatement before eachCREATE CREATE DIRECTED QUERYstatement. For example, you might modify the script generated earlier with the changes shown in bold:SAVE QUERY SELECT employee_dimension.employee_first_name, ... DROP DIRECTED QUERY findBostonCashiers_OPT CREATE DIRECTED QUERY CUSTOM findBostonCashiers_OPT COMMENT 'Optimizer-generated ... DEACTIVATE DIRECTED QUERY findBostonCashiers_OPT; SAVE QUERY SELECT employee_dimension.employee_first_name, ... DROP DIRECTED QUERY findBostonCashiers_CUSTOM CREATE DIRECTED QUERY CUSTOM findBostonCashiers_CUSTOM COMMENT 'Custom directed query'... ACTIVATE DIRECTED QUERY findBostonCashiers_CUSTOM;
- When you run this script, Vertica recreates the directed queries and restores their activation status:
=> \i /home/dbadmin/export_directedqueries SAVE QUERY DROP DIRECTED QUERY CREATE DIRECTED QUERY DEACTIVATE DIRECTED QUERY
SAVE QUERY DROP DIRECTED QUERY CREATE DIRECTED QUERY DEACTIVATE DIRECTED QUERY
The script that EXPORT_CATALOG creates specifies to recreate all directed queries with CREATE DIRECTED QUERY CUSTOM, regardless of how they were created originally.