Rules for RowID Joins
The rowID join is a special form of the nested join. The Optimizer selects a rowID join instead of a nested join when the first condition in the query specifies a literal for the first table. This value is then used to select a small number of rows which are then equijoined with a secondary index from the second table.
The Optimizer can select a rowID join only if both of the following conditions are true:
- The WHERE clause condition must match another column of the first table to a NUSI, USI, or join index (if the join index has a rowID of the base table) of the second table.
- Only a subset of the NUSI or USI values from the second table are qualified via the join condition (this is referred to as a weakly selective index condition), and a nested join is done between the two tables to retrieve the rowIDs from the second table.
RowID Join Process
Consider the following generic SQL query:
SELECT * FROM table_1, table_2 WHERE table_1.NUPI = value AND table_1.column = table_2.weakly_selective_NUSI;
The process involved in solving the join steps for this request is as follows:
- The qualifying table_1 rows are duplicated on all AMPS.
- The value in the join column of a table_1 row is used to hash into the table_2 NUSI (similar to a nested join).
- The rowIDs are extracted from the index subtable and placed into a spool together with the corresponding table_1 columns. This becomes the left table for the join.
- When all table_1 rows have been processed, the spool is sorted into rowID sequence.
- The rowIDs in the spool are then used to extract the corresponding table_2 data rows.
- table_2 values in table_2 data rows are put in the results spool together with table_1 values in the rowID join rows.
Stages 2 and 3 are part of a nested join. Stages 4, 5, and 6 describe the rowID join.
The following graphic demonstrates a rowID join:
RowID Join Example
Assume that you submit the following SELECT request. The first WHERE condition is on a NUPI and the second is on a NUSI. The Optimizer applies a rowID join to process the join.
SELECT * FROM table_1, table_2 WHERE table_1.column_1 = 10 AND table_1.column_3 = table_2.column_5;