Slow Path Local Nested Join Process
The following list documents the process applied by the slow path nested join algorithm:
- Read each row from the left table.
- Evaluate each left table row against the right table index value.
- Retrieve the right table index rows that correspond to the matching right table index entries.
- Retrieve the rowIDs for the right table rows to be joined with left table rows from the qualified right table index rows.
- Read the right table data rows using the retrieved rowIDs.
- Produce the join rows.
- 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.