Vertica Analytics Platform Version 9.2.x Documentation
When Vertica processes a query that contains a view, it treats the view as a subquery. Vertica executes the query by expanding it to include the query in the view definition. For example, Vertica expands the query on the view
myview shown in Using Views, to include the query that the view encapsulates, as follows:
=> SELECT * FROM (SELECT SUM(annual_income), customer_state FROM public.customer_dimension WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact) GROUP BY customer_state ORDER BY customer_state ASC) AS ship where sum > 2000000000;
If you query a view and your query only includes columns from a subset of the tables that are joined in that view, Vertica executes that query by expanding it to include only those tables. This optimization requires one of the following conditions to be true:
- Join columns are foreign and primary keys.
- The join is a left or right outer join on columns with unique values.
View Sort Order
When processing a query on a view, Vertica considers the
ORDER BY clause only in the outermost query. If the view definition includes an
ORDER BY clause, Vertica ignores it. Thus, in order to sort the results returned by a view, you must specify the
ORDER BY clause in the outermost query:
=> SELECT * FROM view‑name ORDER BY view‑column;
One exception applies: Vertica sorts view data when the view includes a
LIMIT clause. In this case, Vertica must sort the data before it can process the
For example, the following view definition contains an
ORDER BY clause inside a
=> CREATE VIEW myview AS SELECT SUM(annual_income), customer_state FROM public.customer_dimension WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact) GROUP BY customer_state ORDER BY customer_state ASC;
When you query the view, Vertica does not sort the data:
=> SELECT * FROM myview WHERE SUM > 2000000000; SUM | customer_state -------------+---------------- 5225333668 | MI 2832710696 | TN 14215397659 | TX 4907216137 | CO 2793284639 | MA 3769455689 | CT 3310667307 | IN 2723441590 | AZ 2642551509 | UT 3330524215 | FL 2128169759 | NV 29253817091 | CA 4581840709 | IL 2806150503 | PA (14 rows)
To return sorted results, the outer query must include an
ORDER BY clause:
=> SELECT * FROM myview WHERE SUM > 2000000000 ORDER BY customer_state ASC; SUM | customer_state -------------+---------------- 2723441590 | AZ 29253817091 | CA 4907216137 | CO 3769455689 | CT 3330524215 | FL 4581840709 | IL 3310667307 | IN 2793284639 | MA 5225333668 | MI 2128169759 | NV 2806150503 | PA 2832710696 | TN 14215397659 | TX 2642551509 | UT (14 rows)
If Vertica does not have to evaluate an expression that would generate a run-time error in order to answer a query, the run-time error might not occur.
For example, the following query returns an error, because
TO_DATE cannot convert the string
F to the specified date format:
=> SELECT TO_DATE('F','dd mm yyyy') FROM customer_dimension; ERROR: Invalid input for DD: "F"
Now create a view using the same query:
=> CREATE VIEW temp AS SELECT TO_DATE('F','dd mm yyyy') FROM customer_dimension; CREATE VIEW
In many cases, this view generates the same error message. For example:
=> SELECT * FROM temp; ERROR: Invalid input for DD: "F"
However, if you query that view with the
COUNT function, Vertica returns with the desired results:
=> SELECT COUNT(*) FROM temp; COUNT ------- 100 (1 row)
This behavior works as intended. You can create views that contain subqueries, where not every row is intended to pass the predicate.
Was this topic helpful?