View execution

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;

View optimization

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;

For example, the following view definition contains an ORDER BY clause inside a FROM subquery:

=> 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)

Run-time errors

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.