17.10 - Eliminating Set Operation Branches - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

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.

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


     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.