Join order optimization

The SQE optimizer allows join reordering for a join logical file. However, the join order is fixed if CQE runs a query that references a join logical file. The join order is also fixed if the OPNQRYF JORDER(*FILE) parameter is specified. In addition, the join order is fixed if the query options file (QAQQINI) FORCE_JOIN_ORDER parameter is *YES

Otherwise, the following join ordering algorithm is used to determine the order of the tables:

  1. Determine an access method for each individual table as candidates for the primary dial.
  2. Estimate the number of rows returned for each table based on local row selection.

    If the join query with ordering or grouping is processed in one step, the table with the ordering or grouping columns is the primary table.

  3. Determine an access method, cost, and expected number of rows returned for each join combination of candidate tables as primary and first secondary tables.
    The join order combinations estimated for a four table inner join would be: 
    1-2    2-1    1-3    3-1    1-4    4-1   2-3    3-2    2-4    4-2    3-4    4-3
  4. Choose the combination with the lowest join cost and number of selected rows or both.
  5. Determine the cost, access method, and expected number of rows for each remaining table joined to the previous secondary table.
  6. Select an access method for each table that has the lowest cost for that table.
  7. Choose the secondary table with the lowest join cost and number of selected rows or both.
  8. Repeat steps 4 through 7 until the lowest cost join order is determined.
Note: After dial 32, the optimizer uses a different method to determine file join order, which might not be the lowest cost.

When a query contains a left or right outer join or a right exception join, the join order is not fixed. However, all from-columns of the ON clause must occur from dials previous to the left or right outer or exception join. For example:

FROM A INNER JOIN B ON A.C1=B.C1 
LEFT OUTER JOIN C ON B. C2=C.C2

The allowable join order combinations for this query would be:

1–2–3, 2–1–3, or 2–3–1

Right outer or right exception joins are implemented as left outer and left exception, with files flipped. For example:

FROM A RIGHT OUTER JOIN B ON A.C1=B.C1
is implemented as B LEFT OUTER JOIN A ON B.C1=A.C1. The only allowed join order is 2–1.