16.10 - Projection Pushdown - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

Pushing projections can allow views to be folded that would remain as spools otherwise. For example, views containing CASE expressions are not always merged. However, if these expressions can be removed, the system might be able to perform additional rewrites through the use of view folding.

Query Rewrite cannot push a projection into a view if the request for the view specifies either DISTINCT or NORMALIZE.

Examples of Projection Pushdown

In this example, the only column of the view sales_by_product that is referenced is total, so projection pushdown can remove both product_key and product_name from the select list of sales_by_product. Note that these column specifications must be retained in the GROUP BY clause for the view.

     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 SUM(total) AS total_sales
     FROM sales_by_product;

Furthermore, because sales_by_product only references sales_product_key, quantity, and amount in the sales view select list, all of the other columns (sales_key, sales_date, and sales_store_key) can be removed from each branch of the sales view. These columns can be removed safely only because the view is a UNION ALL view. Projection pushdown cannot, however, be applied to UNION DISTINCT views because the elimination of duplicates is based on the select lists for the branches. This rewrite also cannot be applied to views defined with an explicit DISTINCT operator.

You can see from this example that projection pushdown can have a cascading effect, because removing columns from the select list of a containing query block can cause columns in the select lists of nested views to become dereferenced.

Another interesting case occurs when no columns of the view are referenced. This example again uses the view sales_by_product.

     SELECT COUNT(*) AS cnt
     FROM sales_by_product;

The request does not reference any columns from sales_by_product. As a result, the select list of sales_by_product can be rewritten as SELECT 0, because the rewrite only needs to ensure that the correct number of rows is returned.

Another special case, again based on the sales_by_product view, occurs when no columns of a view or derived table are referenced by the request, and the view is guaranteed to return only one row.

     SELECT COUNT(*) AS cnt
     FROM (SELECT SUM(total) AS total_sales
           FROM sales_by_product) AS dt ;

No rows of the derived table dt are referenced in this request, so the view contains only a single row because its select list specifies only an aggregate function. Such views are called single-row views. Projection pushdown rewrites this request as follows.

     SELECT COUNT(*) AS cnt
     FROM (SELECT 0 AS dummy_col) AS dt ;

Spooling views with dereferenced columns is a common practice in query rewrite and optimization, and projection pushdown should help the performance of these cases by removing any dereferenced columns, thus reducing the size of spools.