Partial Covering Multitable Join Indexes - Teradata Database

Partial Covering Multitable Join Indexes

Teradata Database optimizes queries to use a join index on a set of joined tables even if the index does not completely cover the columns referenced in the table if the following things are true.

The index includes either the Row ID or the columns of a unique index on the table containing a non‑covered column referenced by the query.

The cost of such a plan is less than other competing query plans.

A partial covering multitable join index provides some of the query improvement benefits that covering join indexes offer without replicating all of the table columns required to cover requests in the join index, but an additional overhead from having to access base table rows to retrieve column values occurs when a non‑covered column is specified in a request.