16. Using the Query Optimizer : Types of Nodes in a QEP : Join Nodes in a QEP : Partial Sort Merge Node
 
Share this page                  
Partial Sort Merge Node
The partial sort merge (PSM) is a cross between a full sort merge and a cart-prod. The inner tree must be in sorted order. The outer tree can be sorted or partially sorted. The outer tree in PSM scenarios can always be derived from an ISAM table. Comparisons proceed as for the full sort merge until an outer value is found to be out of order. At that point the inner loop is restarted. Because ISAM tables are reasonably well ordered (depending on how many rows have been added because the last reorganization), the number of inner loop restarts is typically small.
A typical abbreviated example of a QEP diagram involving a PSM is shown below:
             Join
          /      \
      proj-rest  sort
      /           /
    table     proj-rest
                  /
                table
This node is displayed with the following information on a QEP diagram:
A label identifying it as a PSM join node, along with the columns on which processing is done
If an outer join has been requested, one of the following labels indicating the type of join:
[LEFT JOIN]
[FULL JOIN]
[RIGHT 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)
The following example shows a QEP diagram with a PSM join:
SELECT a.col2, b.col2 FROM arel a, brel b
WHERE a.col1 = b.col2;
 
QUERY PLAN 6,1, no timeout, of main query
                     PSM Join(col1)
                     Heap
                     Pages 1 Tups 156
                     D9 C26
                    /          \
           Proj-rest            Proj-rest
           Heap                 Sort on(col1)
           Pages 1 Tups 156     Pages 1 Tups 156
           D1 C1                D8 C1
          /                     /
       brel                  arel
       Isam(col2)            Hashed(NU)
       Pages 3 Tups 156      Pages 70 Tups 156