12. Using the Query Optimizer : Types of Nodes in a QEP : Join Nodes in a QEP : Key and Tid Lookup Join Node
 
Share this page                  
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