When you are joining four or fewer tables, Adaptive Server considers all possible permutations of join orders for the tables. However, due to the iterative nature of Adaptive Server’s optimizer, queries on more than four tables examine join order combinations in sets of two to four tables at a time. This grouping during join order costing is used because the number of permutations of join orders multiplies with each additional table, requiring lengthy computation time for large joins. The method the optimizer uses to determine join order has excellent results for most queries and requires much less CPU time than examining all permutations of all combinations.
If the number of tables in a join is greater than 25, Adaptive Server automatically reduces the number of tables considered at a time. Table 23-1 shows the default values.
Tables joined |
Tables considered at a time |
|---|---|
4 – 25 |
4 |
26 – 37 |
3 |
38 – 50 |
2 |
The optimizer starts by considering the first two to four tables, and determining the best join order for those tables. It remembers the outer table from the best plan involving the tables it examined and eliminates that table from the set of tables. Then, it optimizes the best set of tables out of the remaining tables. It continues until only two to four tables remain, at which point it optimizes them.
For example, suppose you have a select statement with the following from clause:
from T1, T2, T3, T4, T5, T6
The optimizer looks at all possible sets of 4 tables taken from these 6 tables. The 15 possible combinations of all 6 tables are:
T1, T2, T3, T4 T1, T2, T3, T5 T1, T2, T3, T6 T1, T2, T4, T5 T1, T2, T4, T6 T1, T2, T5, T6 T1, T3, T4, T5 T1, T3, T4, T6 T1, T3, T5, T6 T1, T4, T5, T6 T2, T3, T4, T5 T2, T3, T4, T6 T2, T3, T5, T6 T2, T4, T5, T6 T3, T4, T5, T6
For each one of these combinations, the optimizer looks at all the join orders (permutations). For each set of 4 tables, there are 24 possible join orders, for a total of 360 (24 * 15) permutations. For example, for the set of tables T2, T3, T5, and T6, the optimizer looks at these 24 possible orders:
T2, T3, T5, T6 T2, T3, T6, T5 T2, T5, T3, T6 T2, T5, T6, T3 T2, T6, T3, T5 T2, T6, T5, T3 T3, T2, T5, T6 T3, T2, T6, T5 T3, T5, T2, T6 T3, T5, T6, T2 T3, T6, T2, T5 T3, T6, T5, T2 T5, T2, T3, T6 T5, T2, T6, T3 T5, T3, T2, T6 T5, T3, T6, T2 T5, T6, T2, T3 T5, T6, T3, T2 T6, T2, T3, T5 T6, T2, T5, T3 T6, T3, T2, T5 T6, T3, T5, T2 T6, T5, T2, T3 T6, T5, T3, T2
Let’s say that the best join order is determined to be:
T5, T3, T6, T2
At this point, T5 is designated as the outermost table in the query.
The next step is to choose the second-outermost table. The optimizer eliminates T5 from consideration as it chooses the rest of the join order. Now, it has to determine where T1, T2, T3, T4, and T6 fit into the rest of the join order. It looks at all the combinations of four tables chosen from these five:
T1, T2, T3, T4 T1, T2, T3, T6 T1, T2, T4, T6 T1, T3, T4, T6 T2, T3, T4, T6
It looks at all the join orders for each of these combinations, remembering that T5 is the outermost table in the join. Let’s say that the best order in which to join the remaining tables to T5 is:
T3, T6, T2, T4
So the optimizer chooses T3 as the next table after T5 in the join order for the entire query. It eliminates T3 from consideration in choosing the rest of the join order.
The remaining tables are:
T1, T2, T4, T6
Now we’re down to 4 tables, so the optimizer looks at all the join orders for all the remaining tables. Let’s say the best join order is:
T6, T2, T4, T1
This means that the join order for the entire query is:
T5, T3, T6, T2, T4, T1
