Local Nested Join | Join Planning/Optimization | Teradata Vantage - 17.10 - Local Nested Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Definition of the Local Nested Join

Use of a local nested join implies several things:
  • If necessary, the resulting rows of a nested join are redistributed by row hashing the rowID of the right table rows.
  • The rowID is used to retrieve the data rows from the right table.
The following local nested join algorithms are available:

Join Process as a Function of Secondary Index Type on Equality Conditions

A local nested join can be applied by the Optimizer if there is an equality condition on a NUSI or USI of one of the join tables.

Whether the equality condition is made on a USI or a NUSI, stages 3 and 4 in the following process tables (the rowID join) are not always required, depending on the situation. For more information on rowID joins, see RowID Join.

IF the equality condition is on this index type … THEN the left table is …
USI
  1. Hash-redistributed based on the joined column.
  2. Nested joined with the right table.
  3. The resulting rows are redistributed by row hashing the rowID of the right table rows.
  4. The rowID is used to retrieve the data rows from the right table to complete the join.
NUSI
  1. Duplicated on all AMPs.
  2. Nested joined with the right table.
  3. The resulting rows are redistributed by row hashing the rowID of the right table rows.
  4. The rowID is used to retrieve the data rows from the right table to complete the join.