Projection Pushdown - Advanced SQL Engine - Teradata Database

Pushing projections can allow views to be folded that would remain as spools otherwise. (For more information on view folding, see View Folding.) 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.

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;

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 ;

Projection pushdown should help the performance of spooling views by removing any dereferenced columns, thus reducing the size of spools.