15.10 - Local Nested Join - 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

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.

    • Two different local nested join algorithms are available:

  • Slow path (see “Slow Path Local Nested Join” on page 452)
  • Fast path (see “Fast Path Local Nested Join” on page 455)

    • 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” on page 477.

     

    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.