The Optimizer does the following when rewriting requests using a join index:
- 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), and extended grouping sets.
When you create multiple aggregate join indexes, the creation of the current aggregate join index uses an existing aggregate join index that is most efficient for the calculation of the aggregate join index being created 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 queries 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.