Rules and Restrictions for Join Indexes - Teradata Vantage

Restriction on Number of Join Indexes Selected by the Optimizer Per Table

The Optimizer can use several join indexes for a single query, selecting one or more multitable join indexes and additional single-table join indexes for its join plan. The selection depends on the query structure, and the Optimizer may not choose all applicable join indexes for the plan. EXPLAIN reports show which join indexes are used. If a join index you expected a query to use is not in the join plan, restructure the query and EXPLAIN it again.

The join planning process selects a multitable join index to replace an individual table in a query when the substitution further optimizes the query plan. For each such table, as many as two additional single-table join indexes can 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 make sure that the optimization is worth the effort. Therefore, the time spent generating the query plan must not exceed the accrued performance enhancement gained from the optimization.

Permanent Journal Recovery of Join Indexes

You can use ROLLBACK or ROLLFORWARD statements to recover base tables that have join indexes defined on them. However, the join indexes are marked invalid, not rebuilt, during the recovery process. You must drop and recreate such join indexes before the Optimizer can use them again.

When a join index has been marked not valid, the SHOW JOIN INDEX statement displays a special status message to inform you that the join index has been so marked.

Restrictions on Creating a Join Index on a Table Concurrent with Dynamic AMP Sample Emulation on that Table

You cannot create a join index for a table while that table is subject to dynamic AMP sample emulation. To disable dynamic AMP sampling, contact Teradata Support.