16.10 - View Folding - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

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, many views cannot be folded. For example, a view with aggregation that is joined to another table must be spooled.

Query Rewrite supports 2 types of view folding: Type 1 and Type 2.

Type 1 view folding is defined by exclusion to mean all view folding that is not Type 2 view folding.

Type 2 view folding is used to rewrite the following set of special expressions that are not handled by Type 1 view folding:

  • Views and derived tables that have constants or null manipulating expressions in their select lists.

    Null-manipulating expressions include all of the following types:

    • CASE (see SQL Functions, Operators, Expressions, and Predicates)
    • COALESCE (see SQL Functions, Operators, Expressions, and Predicates )
    • ZEROIFNULL (see SQL Functions, Operators, Expressions, and Predicates)
    • UDFs (see SQL Data Definition Language and SQL External Routine Programming)
    • Methods (see SQL Data Definition Language and SQL External Routine Programming)

      Constants and null-manipulating expressions are collectively referred to as null-sensitive expressions.

  • Views and derived tables specified as inner tables in outer join operations.

All other view and derived table folding is handled by Type 1 view folding.

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
     SELECT *
     FROM sales2
     SELECT *
     FROM sales12;

The requirement for this rewrite to take place is that the containing block be of the following form:

     SELECT <column_list> FROM <UNION ALL view>

In this case there can only be a SELECT and a FROM clause in the view definition, and column_list is a set of columns that exactly matches the columns in the select lists of the UNION ALL view. If the set of columns in the containing query is a subset of the columns in the UNION ALL view, the rewrite still occurs because Query Rewrite applies projection pushdown before view folding, so the unreferenced columns are removed from the view, leaving behind an exact match between the sets of columns.

The following example illustrates the need for Type 2 view folding. The rewrite uses Type 2 view folding because the view contains the null-sensitive expression ZEROIFNULL(quantity), it refers to an expression in the select list of the containing block (qty), and it is on the inner side of an outer join.

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 ;

A Type 1 rewrite of this request 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. Type 2 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 using Type 2 view folding:

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

Rules for Folding Views

To fold a view or derived table, Query Rewrite first determines whether it can be folded. If it can, then Query Rewrite operates on the view or derived table in the following stages:

  1. Removes the table reference of the folded view or derived table from the containing query block.
  2. Removes the view reference of the folded view or derived table from the view list of the containing query block.
  3. Merges the FROM clause of the containing block and the view or derived table.
  4. Splits the WHERE clause of the containing block into the following groups of conditions:
    1. Those that can remain in the WHERE clause.
    2. Those that must be moved to the HAVING clause of the containing query block, for example, when the condition references an aggregate in the view or derived table.
  5. Merges the WHERE and HAVING clauses of the containing block and the view or derived table.
  6. Sets the DISTINCT flag of the containing query block to TRUE if the view or derived table is a SELECT DISTINCT. This is done for simple view merging only.
  7. Changes all references to the view or derived table select list to references to the expressions these references map to in the assignment list of the view or derived table.
  8. Moves any SAMPLE, QUALIFY, and GROUP BY clauses in the view or derived table to the containing query block.
  9. If there is a joined table tree for the containing query block, modify it to point to one of the following structures:
IF the view or derived table … THEN modify it to point to this structure …
is a single-table view or derived table the table specified in the view definition.
specifies multiple tables but does not specify an outer join a joined table tree of inner joins.
specifies an outer join the joined table tree of the view or derived table.

The algorithm for folding views is driven by conditions would otherwise call for spooling a view. The exception is the class of queries called simple queries, which are defined as queries with a direct retrieval from a single view.

This case can be formally defined as follows:

  • The view is the only object in the query.
  • The view is not a derived table.

    The system spools derived tables unconditionally.

  • The query does not have any clause other than a SELECT and a FROM.

    This means that the main query cannot have any of the following operators or clauses:

    • SAMPLE
    • TOP n
    • WHERE
    • HAVING
    • ORDER BY
    • GROUP BY
  • The main query can have only SELECT * or select list of columns from the view

The logic of view folding checks for a list of conditions to spool a view:

  • If the searched conditions are found, then Query Rewrite spools the view.
  • If the searched conditions are not found, then Query Rewrite folds the view.

The list of spooling conditions is summarized in the following list. Note that the list is checked only if the query is not a simple query.

The conditions that invoke spooling if any such conditions exist in the query are as follows:

  • Views with aggregates, meaning those having a GROUP BY clause, HAVING clause, WITH … BY clause, or aggregates in the select list, are spooled if any of the following conditions are satisfied:
    • The query is an ABORT or ROLLBACK statement.

      The ABORT and ROLLBACK statements have a simple syntax that does not include clauses like GROUP BY and HAVING. Folding an aggregate view in this case could trigger an error for the ABORT or ROLLBACK statement, which is why they are spooled in this case.

    • The main query has aggregations.

      Folding views in this case could cause nested aggregations, which the system does not support. It could also cause 2 levels of GROUP BY, in which both the view and the main query have a GROUP BY clause, which the system does not support.

    • The main query has windowing statistical functions. The reasoning is identical to that for main query aggregations.
    • The view has extended grouping sets.

      A side effect of folding a view is that conditions can be pushed into the tables referenced in the view. This could be a problem for views with extended grouping sets.

    • Both the main query and the view have outer joins, and the view has a WHERE clause.
    • The view is the only object in the main query, but it references a constant in the definition and has no GROUP BY or HAVING clause.
  • Views with a DISTINCT operator.

    In general, a DISTINCT operator in the view cannot pulled up to the main query, so such views must be spooled.

  • The view is defined on a single table with a WHERE clause and is part of a full outer join.
  • The view is an inner table of an outer join in the main query, and has a constant, CASE, or ZEROIFNULL plus one of the following conditions:
    • The view is actually a derived table.
    • The view is part of a full outer join.
    • The constant is casting to a different data type.
    • The constant, CASE, or ZEROIFNULL expression is referenced in an aggregate of the main query.
  • The view has any set operations such as the following:
    • UNION
  • The view has windowing statistical functions, a QUALIFY clause, or a SAMPLE clause.
  • Either the main query or the view has the TOP n operator.
  • The main query is an UPDATE or DELETE and the view definition contains outer joins.

    This is excluded because neither UPDATE nor DELETE statements allow outer joins.

Query Rewrite does not fold a view if the view specifies either DISTINCT or NORMALIZE.