16.20 - Restriction on Number of Join Indexes Selected by the Optimizer Per Table - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

The Optimizer can use several join indexes for a single query, selecting one or more multitable join indexes as well as 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. Always examine your EXPLAIN reports to determine which join indexes are used for the join plans generated for your queries. If a join index you think should have been used by a query was not included in the join plan, try restructuring the query and EXPLAIN it once again.

The join planning process selects a multitable join index to replace any individual table in a query when the substitution further optimizes the query plan. For each such table replaced in the join plan by a multitable join index, as many as two additional single-table join indexes can also be considered as replacements if their inclusion reduces the size of the relation to be processed, provides a better distribution, or offers additional covering.

The limit on the number of join indexes substituted per individual table in a 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 ensure that the optimization is worth the effort. This means that the time spent generating the query plan should not exceed the accrued performance enhancement gained from the optimization.