15.00 - More On Outer Join Index Coverage of Queries - Teradata Database

Teradata Database Design

Teradata Database
User Guide

More On Outer Join Index Coverage of Queries

A join index can to be used to cover a wide variety of queries as long as the rows required in these queries form a subset of the row set contained in the join index. For example, consider a join index defined with the following SELECT query, where x1, x2 is a foreign key‑primary key pair:

     CREATE JOIN INDEX loj_cover AS
     SELECT x1, x2
     FROM t1, t2 
     WHERE x1=x2; 

Any query of the following form can use this join index, where c represents any set of constant conditions:

     SELECT x1, x2
     FROM t1 LEFT OUTER JOIN t2 ON x1=x2 AND c; 

This property greatly increases the applicability of many join indexes.

Both the join index and the query are normalized to inner joins when the original form is defined with an outer join and there is a foreign key‑primary key relationship between the join column set (see “Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query” on page 577). The result is that a less restrictive coverage test can be applied to both the query and to the join index.