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, views cannot always be folded. For example, a view with aggregation that is joined to another table must be spooled.
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 UNION ALL SELECT * FROM sales2 UNION ALL … UNION ALL SELECT * FROM sales12;
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 ;
Simply folding the view 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. 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:
SELECT product_name, SUM(CASE WHEN sales1.ROWID IS NULL THEN NULL ELSE quantity END) FROM product LEFT OUTER JOIN sales1 ON product_key=sales_product_key GROUP BY product_key, product_name;