|
About the optimizer's choice of join order
The optimizer chooses the optimal join order as well as the optimal index
for each table. The join order can affect which index is the best choice.
The optimizer can choose an index as the access path for a table if it is
the inner table, but not if it is the outer table (and there are no further
qualifications).
The optimizer chooses the join order of tables only in simple FROM clauses.
Most joins using the JOIN keyword are flattened into simple joins, so the
optimizer chooses their join order.
The optimizer does not choose the join order for outer joins; it uses the
order specified in the statement.
When selecting a join order, the optimizer takes into account:
- The size of each table
- The indexes available on each table
- Whether an index on a table is useful in a particular join order
- The number of rows and pages to be scanned for each table in each join
order
Note: Derby does transitive closure on qualifications.
For details, see Transitive closure.
|
