Join Index Types - Teradata Database

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 partitioned primary index, but keep in mind that you can define a join index with a partitioned primary index only if it is not row compressed. See “Join Index Applications” on page 455. You can define a join index with column partitioning and/or 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 163 and Chapter 10: “Join and Hash Indexes.”