System Processing of Join Indexes - Teradata Database

System Processing of Join Indexes

The Optimizer does the following when it rewrites requests using a join index.

Selects cost-based query rewrites using the best available aggregate join index when several 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 makes use of an existing aggregate join index that is the 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 it possible to produce better join plans.