When the optimizer creates a query plan for a join query:
It evaluates indexes for each table by estimating the I/O required for each possible index and for a table scan.
It determines the join order, basing the decision on the total cost estimates for the possible join orders. It estimates costs for both nested-loop joins and sort-merge joins.
If no useful index exists on the inner table of a join, the optimizer may decide to build a temporary index, a process called reformatting.
It determines the I/O size and caching strategy.
It also compares the cost of serial and parallel execution, if parallel query processing is enabled.
See Chapter 25, “Parallel Query Optimization,” for more information.
Factors that determine costs on single-table selects, such as appropriate indexing, search argument selectivity, and density of keys, become much more critical for joins.
