Representing a fact table as a UNION ALL view of horizontal partitions of fact table rows is a commonly used technique.
These UNION ALL views are often joined to constrained dimension tables as shown in the following example view and query against that view:
CREATE VIEW jan_feb_sales AS SELECT * FROM sales1 UNION ALL SELECT * FROM sales2; SELECT SUM(quantity*amount) AS total FROM jan_feb_sales, product WHERE sales_product_key=product_key AND product_name LIKE 'French%';
Query Rewrite does not push joins to UNION ALL views when either of the following conditions is true:
- The SELECT request on a view specifies the DISTINCT operator.
- The SELECT request on a view specifies the NORMALIZE option.
In this example of a UNION ALL rewrite, the join with the constrained dimension table can be pushed into each branch of the UNION ALL. The rewrite of this example is as follows:
SELECT SUM(quantity*amount) AS total FROM (SELECT quantity, amount FROM sales1, product WHERE sales_product_key=product_key AND product_name LIKE 'French%' UNION ALL SELECT quantity, amount FROM sales2, product WHERE sales_product_key=product AND product_name LIKE 'French%' ) AS jan_feb_sales ;
The rewritten query can reduce the size of the spool for the view by using the constrained join to filter rows from sales1 and sales2 before writing the spool. This rewrite is cost-based (see Cost Optimization), so the Join Planner is called by the Query Rewrite Subsystem to determine whether the original or rewritten version of a request can be executed more efficiently.