Eliminating Set Operation Branches - Teradata Vantage

This rewrite removes branches of set operations that contain unsatisfiable conditions (typically derived by SAT-TC), if possible.

Eliminating Branches with Unsatisfiable Conditions

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

     UNION ALL

     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.