15.00 - Join Index Types - Teradata Database

Teradata Database Design

Teradata Database
Release Number
Content Type
User Guide
Publication ID
English (United States)

Join Index Types

There are two basic types of join index:

  • Multitable join indexes are defined for join queries that are performed frequently enough to justify defining a prejoin on the joined columns.
  • Single-table join indexes are defined to facilitate joins by hashing a frequently joined subset of base table columns to the same AMP as the table rows to which they are frequently joined. This enhanced geography eliminates BYNET traffic as well as often providing a smaller sized row to be read and joined.
  • You can define a join index with either a nonpartitioned or row-partitioned primary index, but keep in mind that you can define a join index with row-partitioned primary index only if it is not row compressed. See “Join Index Applications” on page 504. You can also define a join index with column partitioning (and no primary index) with optional row partitioning.

    The MultiLoad and FastLoad utilities do not support target tables with join indexes. The workaround for this is to FastLoad new rows into an empty table and then use either a INSERT … SELECT or MERGE request to populate the indexed table (see SQL Data Manipulation Language for details).

    For additional information, see “Join Indexes” on page 215 and Chapter 11: “Join and Hash Indexes.”