15.10 - Pushing Joins Into UNION ALL Branches - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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.