16.10 - Slow Path Local Nested Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

Slow Path Local Nested Join Process

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:

The following 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;
e_num e_name dept
1 Brown 200
2 Smith 310
3 Jones 310
4 Clay 400
5 Peters 150
6 Foster 400
7 Gray 310
8 Baker 310
dept dept_name
400 Education
150 Payroll
200 Finance
310 Mfg