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

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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:


Slow path local nested join process (steps 1-2)


Slow path local nested join process (step 3)

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:


Two-AMP process

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;
Employee Table
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

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

Department Table
dept dept_name
400 Education
150 Payroll
200 Finance
310 Mfg

Column dept is the UPI and PK for the table.