Key and Tid Lookup Join Node
In key and tid lookup joins, the outer and inner data set is not static. For each outer row, the join selects values and forms a key to search in the inner join. A key lookup join uses keyed access through the structure of the inner table or index, and a tid lookup join uses the tuple identifier (tid) value.
A typical abbreviated example of a QEP diagram involving this type of join is shown below:
Join
/ \
sort btree (or hash or isam)
\
proj-rest
\
table
This node is displayed with the following information on a QEP diagram:
• A label identifying it as a key (K) or tid (T) lookup join, along with the column(s) on which processing is done
• If an outer join has been requested, the following label indicating the type of join:
[LEFT JOIN]
• Storage structure (which is usually heap)
• Total number of pages and tuples
• Query cost information
• Optionally, sort information (whether the data produced by this node is already sorted or requires sorting)
This case is seen most frequently where the outer subtree proj-rest returns few rows, so it is faster to do a keyed lookup (on an ISAM, hash, or B-tree table) than any of the sort merge operations.
The following example shows a QEP diagram with a key lookup join:
SELECT b.col1, b.col2, a.col2
FROM arel a, brel b
WHERE a.col3 = 'x' AND a.col1 = b.col1;
K Join(col1)
Heap
Pages 1 Tups 2
D3 C0
/ \
Proj-rest brel
Heap Isam(col1)
Pages 1 Tups 2 Pages 3 Tups 156
D1 C0
/
arel
Hashed(NU)
Pages 70 Tups 156
In the next example of a tid lookup join, access to the base table is through the secondary index, and proj-rest collects tids for sorting. The tids are used for a direct tid lookup in the base table. Therefore, the storage structure of the base table is NU:
SELECT a.col1, a.col2 FROM arel a
WHERE a.col2 = 99
ORDER BY a.col2;
Sort(col2)
Pages 1 Tups 1
D4 C1
/
T Join(tidp)
Heap
Pages 1 Tups 1
D4 C0
/ \
Proj-rest arel
Sort on(tidp) Hashed(NU)
Pages 1 Tups 1 Pages 70 Tups 156
D2 C0
/
aindex
Isam(col2)
Pages 2 Tups 156