15.10 - Fast Path 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

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 …

THEN …

equal

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;