16.10 - Pushing Joins Into UNION ALL Branches - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

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
     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%'


     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.