15.10 - Fast Path Local Nested Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

The process applied by the fast path nested join algorithm is provided in the following table. Note that it is similar to the fast path merge join except that the right table is a NUSI subtable instead of a base table.

This logic returns multiple join rows because there can be multiple rowIDs from the right NUSI subtable for each pair of left and right table rows.

1 Read a row from the left base table and record its hash value.

2 Read the next row from the right NUSI subtable that has a row hash >= to that of the left base table row.


IF the row hash values are …



join the two rows.

not equal

use the larger row hash value to read the row from the right NUSI subtable.

The following SELECT request is an example of a query that is processed using a very simple fast path local nested join:

   SELECT * 
   FROM table_1, table_2 
   WHERE table_1.x_1 = 10 
   AND   table_1.y_1 = table_2.NUSI;