15.10 - Slow 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 following list documents the process applied by the slow path nested join algorithm:

1 Read each row from the left table.

2 Evaluate each left table row against the right table index value.

3 Retrieve the right table index rows that correspond to the matching right table index entries.

4 Retrieve the rowIDs for the right table rows to be joined with left table rows from the qualified right table index rows.

5 Read the right table data rows using the retrieved rowIDs.

6 Produce the join rows.

7 Produce the final join using the left table rows and the right table rowIDs.

The following graphics illustrate the generic slow path local nested join process:

The following is an example of a query processed using a slow path local nested join.

To determine who manages department 100, you could make the following query:

   SELECT dept_name, name, yrs_exp
   FROM employee, department
   WHERE employee.emp_no = department.mgr_no
   AND   department.dept_no = 100;

To process this query, the Optimizer uses the unique primary index value dept_no=100 to access the AMP responsible for the department row with that value. The hash code for the mgr_no value in that row is calculated.

Note that this mgr_no value is the same as the value of emp_no (the unique primary index of the employee table) for the employee who manages department 100. Thus, the hash code that is calculated for mgr_no is the same as the hash code for the equivalent emp_no value.

The calculated hash code for mgr_no is used to access the AMP responsible for the Employee row that contains the equivalent emp_no hash code.

The name and yrs_exp information in this row is sent back to the initiating AMP, which places the information, plus the dept_name for Department 100, in a result spool. This information is returned to the user.

This 2-AMP process is illustrated in the following graphic:

This example shows a query that is processed using a slow path nested join on the employee and department tables. 

     SELECT employee.name, department.name
     FROM employee, department
     WHERE employee.enum = 5
     AND   employee.dept = department.dept;