15.10 - Eliminating Set Operation 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

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