Pushing projections can allow views to be folded instead of remaining as spools (see View Folding). For example, views containing CASE expressions are not always merged. However, if these expressions can be removed, the system may be able to perform additional rewrites through the use of view folding.
Examples: 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. 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;
This example shows 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. Therefore, the select list of sales_by_product can be rewritten as SELECT 0, because the rewrite must make sure only 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 helps the performance of spooling views by removing any dereferenced columns, thus reducing the size of spools.