Join order optimization

The join order is fixed if any join logical files are referenced. The join order is also fixed if the OPNQRYF JORDER(*FILE) parameter is specified or 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:

Determine an access method for each individual table as candidates for the primary dial.
Estimate the number of rows returned for each table based on local row selection.
If the join query with row ordering or group by processing is being processed in one step, then the table with the ordering or grouping columns is the primary table.
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
```
Choose the combination with the lowest join cost and number of selected rows or both.
Determine the cost, access method, and expected number of rows for each remaining table joined to the previous secondary table.
Select an access method for each table that has the lowest cost for that table.
Choose the secondary table with the lowest join cost and number of selected rows or both.
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 may 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.

When a join logical file is referenced or the join order is forced to the specified table order, the query optimizer loops through all of the dials in the order specified, and determines the lowest cost access methods.