- 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!
About the Author
James Knicely
Vertica Field Chief Technologist
I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!
