Projection Pushdown - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.