15.10 - Eliminating Set Operation Branches - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

This rewrite looks for branches of set operations that contain unsatisfiable conditions, which are usually derived by SAT-TC, and then removes those branches if it can.

Suppose you have created table sales2 with the following partial definition.

     CREATE TABLE sales2 (
       sales_date DATE
     CONSTRAINT feb_only CHECK (EXTRACT(MONTH FROM sales_date=2));

Consider the following example.

     SELECT * 
     FROM sales1 
     WHERE EXTRACT(MONTH FROM sales_date)=1
     SELECT * 
     FROM sales2 
     WHERE EXTRACT(MONTH FROM sales_date)=1;

The second branch of the UNION ALL in this example is unsatisfiable because sales2 only contains rows where the value for month in sales_date equals 2. Therefore, the query can be rewritten as follows.

     SELECT * 
     FROM sales1 
     WHERE EXTRACT(MONTH FROM sales_date)=1;

When branches of UNION operations, as opposed to UNION ALL operations, are eliminated, the rewrite adds a DISTINCT to the remaining branch if necessary to guarantee correct results. Unsatisfiable branches of INTERSECT and MINUS operations are also eliminated by this rewrite technique.