To run your SQL query as efficiently as possible, the Query Processor calculates the most effective order in which to access the tables you have referenced. To do this, the Query Processor examines the joins in your query and works out every possible order it could use to access the tables you have referenced. These orders are called permutations, and for each permutation, the Query Processor is able to establish:
- A join order (the order in which the tables are accessed for that permutation)
- An access path (the way in which the Query Processor will read the tables referenced in the permutation)
- The cost (the demand that the permutation places on system resources).
So for each permutation, there is a join order, and one or more access paths. The join order and access paths for a single permutation are referred to as the permutation’s access plan. The Query Processor uses the access plan that has the lowest cost (see Calculate the Cost and Determine the Final Access Plan).
When calculating the join orders for accessing the tables, the Query Processor:
- Calculates the permutations for up to 5 tables. It uses the tables in the order in which they appear in your query and can calculate up to 120 permutations.
So, if you have referenced 7 tables using inner joins, the Query Processor will work out the permutations for the first 5 tables, and will then use the most efficient of these permutations (the remaining 2 tables will be accessed after the first 5 tables in the same order as they appear in your query).
If you have used an outer join for one of the first 5 tables, the Query Processor will only calculate the permutations for the tables that preceded the outer join. Any tables that are referenced after the outer join are accessed in the order in which they appear in your query (even if the 5 table limit has not been reached).
- Calculates the cost of each permutation (see Calculate the Cost and Determine the Final Access Plan). The permutation with the lowest cost places the least demand on system resources. This is the access path that the Query Processor will use to obtain the values needed for your query.
Example:
In the following examples, database tables are represented by the letters A, B, C, D, and E, with A being Table A, B being Table B and so on:
- If your query only has a single join that specifies A JOIN B, the Query Processor will calculate the 2 permutations (A,B; B, A). It will then choose the most efficient of the two permutations as its access path.
- If your query references three tables using two inner joins (A JOIN B JOIN C), the Query Processor calculates the 6 possible permutations.
It then chooses to use the permutation that has the lowest cost.
- If the first two joins in your query specify A JOIN B OUTER JOIN C, the Query Processor will calculate 2 permutations (A,B,C; B, A, C). Any joins that appear after the outer join will be accessed in the order in which they are defined in your query.
The Query Processor will then use the permutation that has the lowest cost.
- If the first five joins in your query specify A JOIN B JOIN C OUTER JOIN D JOIN E, the Query Processor will calculate 6 permutations.
The Query Processor will then use the permutation that has the lowest cost.
The Query Processor does not calculate the permutations for any join that appears after an outer join, even if the limit of 5 joins has not yet been reached. So in this case, Table E is accessed last as the permutations are only calculated for the first 4 tables (the tables referenced before the outer join in the query).
For information on how the Query Processor calculates the cost of each access plan, see Calculate the Cost and Determine the Final Access Plan.