View Folding - Advanced SQL Engine - Teradata Database

View folding is an important query rewrite technique in which a request that references a view is rewritten without making an explicit reference to that view. This eliminates the need to create a spool for the view result and makes it possible for the Join Planner to consider additional join orders (see Optimizer Join Plans).

The semantics of views and derived tables are identical; therefore, any assertion in this section that applies to views applies equally to derived tables, and any mention of views can be substituted for by a reference to derived tables without any change in the truth of the assertion.

However, views cannot always be folded. For example, a view with aggregation that is joined to another table must be spooled.

Examples of View Folding

Consider the following view definition and query against it:

     CREATE VIEW sales_by_product AS
     SELECT product_key, product_name, SUM(quantity*amount) AS total
     FROM sales, product
     WHERE sales_product_key = product_key
     GROUP BY product_key, product_name;

     SELECT product_name
     FROM sales_by_product
     WHERE total > 50000;

There is no need to evaluate the view result separately from the containing query block, so view folding can be applied to yield the following rewritten query:

     SELECT product.product_name
     FROM sales, product
     WHERE sales_product_key=product.product_key
     GROUP BY product.product_key, product.product_name
     HAVING (SUM(quantity * amount))>50000;

Spooling a view, on the other hand, means that the view definition is materialized and then treated as a single relation in the main query.

Query Rewrite attempts to fold views whenever it can because folding a view provides the Optimizer with more options for optimizing the query, while spooling the view does not permit its tables to be joined directly with other tables in the main query.

An important example of view folding is folding UNION ALL views, as illustrated by the following example:

     SELECT *
     FROM sales;

In this example, sales is a view involving 11 UNION ALL operations among the 12 sales months for the year.

This query is rewritten as follows:

     SELECT *
     FROM sales1
     UNION ALL
     SELECT *
     FROM sales2
     UNION ALL
      …
     UNION ALL
     SELECT *
     FROM sales12;

Consider the following view definition and query against it:

     CREATE VIEW jan_sales AS
     SELECT sales_product_key, ZEROIFNULL(quantity) AS qty
     FROM sales1;
     SELECT product_name, SUM(qty)
     FROM product LEFT OUTER JOIN jan_sales
                  ON product_key=sales_product_key
     GROUP BY product_key, product_name ;

Simply folding the view would be incorrect because the value of quantity can be NULL, as it was in sales1 (in which case the value of the ZEROIFNULL expression would be 0), or because there was no match in sales1 for a row in product, in which case the value of the ZEROIFNULL expression would be NULL. View folding solves this problem by tracking whether or not quantity was NULL in sales1 originally (meaning before the outer join was made) and produces the correct value based on this tracking. This is illustrated by the following correct rewrite for the query against the jan_sales view:

     SELECT product_name, SUM(CASE
                                  WHEN sales1.ROWID IS NULL
                                  THEN NULL
                                  ELSE quantity
                              END)
     FROM product LEFT OUTER JOIN sales1
                  ON product_key=sales_product_key
     GROUP BY product_key, product_name;