• Preserve current query plans before a scheduled upgrade.
• Enable you to create query plans that improve optimizer performance.
Since their introduction, users have found new and compelling ways to use directed queries—notably, using them to substitute one query for another. The following discussion focuses on one way you might leverage this capability.
Rewriting join queries
It’s often more efficient to query data from a single denormalized table than by joining multiple tables. The recent introduction of flattened tables in Vertica 8.1 has greatly enhanced the feasibility of this approach. However, users often have little direct control over input queries. In this case, you can create a custom directed query, which redirects join queries to the desired flattened table:1. Save the input query with SAVE QUERY. For example, the following statement saves a query that joins tables T1, T2, and T3 :
SAVE QUERY SELECT T1.a, T2,b, T3.c
FROM T1 JOIN T2 ON T1.x = T2.x
JOIN T3 ON T2.y = T3.y
WHERE T3.description ILIKE '%red%';
2. Create a custom directed query that maps the saved input query to one that queries flattened table T123:
CREATE DIRECTED QUERY CUSTOM 'FindReds'
SELECT a, b, c FROM T123 WHERE description ILIKE '%red%';
3. Activate the directed query:
ACTIVATE DIRECTED QUERY FindReds;
When the directed query is active, the query optimizer maps all queries that match the original input format to the directed query. Instead of executing the original query, Vertica uses the annotated query, which queries flattened table T123.Note: If you redirect queries to a flattened table, be sure to refresh that table at appropriate intervals with REFRESH_COLUMNS, so it captures the latest source table updates.
You can expand the scope of this directed query by using the hint IGNORECONST. When the directed query includes this hint, it can be used to execute queries that search on any string in column T3.description:
1. Modify the original join input query by including the hint IGNORECONST and save it:
SAVE QUERY SELECT T1.a, T2,b, T3.c
FROM T1 JOIN T2 ON T1.x = T2.x
JOIN T3 ON T3.y = T2.y
WHERE T3.description ILIKE 'any-string' /*+IGNORECONST(1)*/;
2. Create a custom directed query similar to the earlier one, modified this time with the IGNORECONST hint:
CREATE DIRECTED QUERY CUSTOM 'FindAnyString'
SELECT a, b, c FROM T123
WHERE description ILIKE 'anystring' /*+IGNORECONST(1)*/;
3. Activate the directed query:
ACTIVATE DIRECTED QUERY FindAnyString;
When the directed query is active, the query optimizer, as before, maps all queries that match the original input format to the directed query. Now, however, query predicates can specify any string. For example:
EXPLAIN SELECT T1.a, T2,b, T3.c
FROM T1 JOIN T2 ON T1.x = T2.x
JOIN T3 ON T3.y = T2.y
WHERE T3.description ILIKE '%green%';
The following active directed query(query name: FindAnyString) is being executed:
CREATE DIRECTED QUERY CUSTOM 'FindAnyString'
SELECT /*+verbatim*/ T123.a, T123.b, T123.c FROM public.T123
WHERE T123.description ~~* '%green%'
...
For More Information
See these resources in the Vertica documentation:• Rewriting Queries in the Administrator’s Guide
• Flattened Tables in Analyzing Data
About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
