Using Outer Joins to Define Join Indexes - Teradata Database

Using Outer Joins to Define Join Indexes

There are several benefits in defining non-aggregate join indexes with outer joins:

Unmatched rows are preserved.

These rows allow the join index to satisfy queries with fewer join conditions than those used to generate the index.

Outer table row scans can provide the same performance benefits as a single-table join index.

For example, the Optimizer can choose to scan the outer table rows of a join index to satisfy a query that only references the outer table provided that a join index scan would be more high-performing than scanning the base table or redistributing rows.