15.10 - Pushing Joins Into UNION ALL Branches - Teradata Database

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 case, 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” on page 304), 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.