16.10 - Local Nested Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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.