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