15.00 - Join Elimination and Referential Integrity - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Join Elimination and Referential Integrity

Join elimination is a process undertaken by the Optimizer to eliminate redundant joins based on information from referential integrity constraints.

The following conditions eliminate a join.

  • A referential integrity relationship exists between the two tables.
  • Request conditions are conjunctive, meaning they are ANDed rather than ORed.
  • This means that if any single condition in an ANDed set fails, the entire condition fails.

  • The request does not contain reference columns from the PK table, other than the PK columns, including the SELECT, WHERE, GROUP BY, HAVING, ORDER BY columns.
  • PK columns in the WHERE clause appear only in PK-FK joins.
  •  

    IF…

    THEN…

    the preceding conditions are met

  • the PK join is removed from the query.
  • all references to the PK columns in the query are mapped to the corresponding foreign key columns.
  • foreign key columns are nullable

    Teradata Database adds a NOT NULL condition to the request.