Restriction on Number of Join Indexes Selected for Each Query - Teradata VantageCloud Lake

Lake - Database Reference

Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549

The Optimizer can use multiple join indexes for a single query, selecting one multiple-table join index and additional single-table join indexes for its join plan. The join indexes selected depend on the structure of the query, and the Optimizer might not choose all applicable join indexes for the plan. Examine your EXPLAIN reports to determine which join indexes are used for the join plans generated for your queries. If a join index you expected a query to use is not in the join plan, restructure the query and EXPLAIN it again.

The limit on the number of join indexes considered per query is enforced to limit the number of possible combinations and permutations of table joins in the Optimizer search space during its join planning phase. The rule helps to make sure that the optimization is worth the effort. That is, that the time spent generating the query plan does not exceed the accrued performance enhancement.