
- Preserve current query plans before a scheduled upgrade. In most instances, queries perform more efficiently after a Vertica upgrade. In the few cases where this is not so, you can use directed queries that you created before upgrading to recreate query plans from the earlier version.
- Enable you to create query plans that improve optimizer performance. Occasionally, you might want to influence the optimizer to make better choices in executing a given query. For example, you can choose a different projection, or force a different join order. In this case, you can use a directed query to create a query plan that preempts any plan that the optimizer might otherwise create.
- Redirect an input query to a query that uses different semantics. For example, map a join query to a SELECT statement that queries a flattened table.
Example:
Developer Jim has a table named VERY_IMPORTANT_DATA:
dbadmin=> SELECT * FROM very_important_data;
do_not_lose_this_info
--------------------------
Secret of Life Encrypted
(1 row)
As an April Fool’s joke, let’s change the output of his query.
dbadmin=> SAVE QUERY SELECT do_not_lose_this_info FROM very_important_data;
SAVE QUERY
dbadmin=> CREATE DIRECTED QUERY CUSTOM 'aprilfools'
dbadmin-> SELECT 'The VERY_IMPORTANT_DATA table has been dropped!' do_not_lose_this_info;
CREATE DIRECTED QUERY
dbadmin=> ACTIVATE DIRECTED QUERY aprilfools;
ACTIVATE DIRECTED QUERY
So now what does our unsuspecting developer Jim see?
dbadmin=> \c - jim
You are now connected as user "jim".
dbadmin=> SELECT do_not_lose_this_info FROM very_important_data;
do_not_lose_this_info
-------------------------------------------------
The VERY_IMPORTANT_DATA table has been dropped!
(1 row)
Ha-Ha!Note: This was just for fun!
Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/DirectedQueries.htm
Have fun!