New Uses for Directed Queries

Directed queries were introduced in Vertica 7.2. Directed queries were originally designed to achieve two goals:
• 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