The Optimizer uses join indexes in ways including the following:
- Selects cost-based query rewrites using the best available aggregate join index when multiple possible aggregate join indexes are available.
- Provides a larger number of opportunities to perform cost-based rewrites of requests using aggregate join indexes for queries with subqueries, spooled derived tables, outer joins, COUNT(DISTINCT) operations, and extended grouping sets.
When a user creates multiple aggregate join indexes, the creation of the current aggregate join index uses an existing aggregate join index that is the most efficient for the calculation of this aggregate join index so that the CREATE JOIN INDEX request has better performance.
With the existence of multiple join indexes, including aggregate join indexes and non-aggregate join indexes, aggregate requests perform better with the cost-based rewrite and more chances to use an aggregate join index.
- Uses join indexes with Partial GROUP BY optimizations during join planning, making better join plans possible.