Pushing Joins Into UNION ALL Branches - Advanced SQL Engine - 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%';

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-Based 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.