Slow Path Local Nested Join | Join Planning/Optimization | Teradata Vantage - Slow Path Local Nested Join - Analytics Database - Teradata Vantage

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;

Column e_num is the UPI and PK for the table, and dept is a FK.

Column dept is the UPI and PK for the table.