15.00 - Using Outer Joins to Define Join Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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.