Definition of the Local Nested Join
Use of a local nested join implies several things:
- If necessary, the resulting rows of a nested join are redistributed by row hashing the rowID of the right table rows.
- The rowID is used to retrieve the data rows from the right table.
The following local nested join algorithms are available:
- Slow path (see Slow Path Local Nested Join)
- Fast path (see Fast Path Local Nested Join)
Join Process as a Function of Secondary Index Type on Equality Conditions
A local nested join can be applied by the Optimizer if there is an equality condition on a NUSI or USI of one of the join tables.
Whether the equality condition is made on a USI or a NUSI, stages 3 and 4 in the following process tables (the rowID join) are not always required, depending on the situation. For more information on rowID joins, see RowID Join.
| IF the equality condition is on this index type … | THEN the left table is … |
|---|---|
| USI |
|
| NUSI |
|