Batch Query Plan Export
Before upgrading to a new Vertica version, you might wish to use directed queries to save query plans for possible reuse in the new database. You cannot predict which query plans are likely candidates for reuse, so you probably want to save query plans for many, or all, database queries. However, you run hundreds of queries each day. Saving query plans for each one to the database catalog through repetitive calls to CREATE DIRECTED QUERY is impractical. Moreover, doing so can significantly increase catalog size and possibly impact performance.
In this case, you can bypass the database catalog and batch export query plans as directed queries to an external SQL file. By offloading query plan storage, you can save any number of query plans from the current database without impacting catalog size and performance. After the upgrade, you can decide which query plans you wish to retain in the new database, and selectively import the corresponding directed queries.
Vertica provides a set of meta-functions that support this approach:
EXPORT_DIRECTED_QUERIESgenerates query plans from a set of input queries, and writes SQL for creating directed queries that encapsulate those plans.IMPORT_DIRECTED_QUERIESimports to the database catalog directed queries from a SQL file that was generated byEXPORT_DIRECTED_QUERIES.