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

Fast Path Local Nested Join Process

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 equal, then join the two rows.
   • If the row hash values are not equal, then 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;