15.00 - Join Indexes - Teradata Database

Teradata Database Design

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

Join Indexes

Join indexes have several uses, including the following.

  • Define a prejoin table on frequently joined columns (with optional aggregation) without denormalizing the database.
  • Create a full or partial replication of a base table with a primary index on a foreign key column table to facilitate joins of very large tables by hashing their rows to the same AMP as the large table.
  • You can also use a column‑partitioned join index to create a full or partial replication of a base table to facilitate various manipulations of the data.

  • Define a summary table without denormalizing the database.
  • You can define a join index on one or several tables. Single-table join index functionality is an extension of the original intent of join indexes, hence the confusing adjective join used to describe single-table join indexes.

    Join indexes, along with secondary indexes, are the only way a single row or a row subset can be retrieved from either an NoPI table or a column‑partitioned table without a full‑table scan, scan of subset of row partitions, or full‑column scan for a table (see “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280).

    Join indexes, like hash indexes, are not indexes in the usual sense of the word. They are system‑maintained tables that cannot be accessed directly using DML requests.

    The Optimizer includes a multitable join index in a plan in the following situations.

  • The index covers all or part of a join query, thus eliminating the need to redistribute rows to make the join.
  • A query requests that one or more columns be aggregated, and an appropriate aggregate join index exists, thus eliminating the need to perform the aggregate computation.
  • The Optimizer includes a join index in a plan in the following situations.

  • The index covers all or part of a join query, thus eliminating the need to redistribute rows to make the join. A join index can be used for a partial cover only if it has been defined using any of the following components.
  • The UPI of the underlying base table.
  • The keyword ROWID.
  • You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics.

  • The NUPI of the underlying base table and the keyword ROWID in a join on a NUPI column.
  • The NUPI of the underlying base table or the USI of the underlying base table.
  • It is preferable to specify NUPI and ROWID over NUPI and USI.

    In this case, the Optimizer uses the ROWID, base table UPI or NUPI, or base table USI to join the join index rows with their underlying base table rows to pick up the base table columns necessary to complete the cover.

  • A query requests aggregation on a column set and an existing single‑table aggregate join index covers the specified aggregation, thus eliminating the need to perform the aggregate computation.