For each base table in a query, the Optimizer performs certain processing phases to decide how a database operation that uses a join index is to be processed.
| Phase | Optimizer Behavior |
|---|---|
| Qualification | The optimizer evaluates up to 10 join indexes to choose the one with the lowest cost. Qualification for the best plan includes one or more of the following benefits:
|
| Analysis of results | The optimizer determines if this plan returns unique results, analyzing only those tables in the query that are used in the join index. |
Subsequent action depends on analysis of the results:
- If the results are unique, the Optimizer skips the sort-delete steps used to remove duplicates.
- If the results are not unique, the Optimizer determines whether eliminating all duplicates can still produce a valid plan, recognizing any case where the following things are true:
- No column_name parenthetical clause exists
- All logical rows are accessed