15.00 - Join Index Optimizations - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Join Index Optimizations

The Optimizer uses join indexes in several ways, including the following.

  • 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) operations, and extended grouping sets.
  • When a user creates 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 this aggregate join index so that the CREATE JOIN INDEX request will have 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 it possible to produce better join plans.