15.00 - Join Indexes Versus NUSIs - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
15.00
Content Type
User Guide
Publication ID
B035-1094-015K
Language
English (United States)

Join Indexes Versus NUSIs

A join index offers the same benefits as a standard secondary index in that it, like the standard secondary index, has the following properties.

  • Optional
  • User defined
  • Maintained by the system
  • Transparent to end users
  • Immediately available to the Optimizer
  • If a covering index, considered by the Optimizer for a merge join
  • However, a join index offers the following performance benefits over a NUSI.

     

    IF a join index is…

    THEN performance improves by…

    defined using joins on one or more columns from two or more base tables

    eliminating the need to perform the join step every time a joining query is processed.

    used for direct access in place of some or all of its base tables, if the Optimizer determines that it covers most or all of the query.

    eliminating the I/Os and resource usage required to access the base tables.

    limited to only certain data types of your choice, such as Date

    allowing direct access to the join index rows within the specified value-order range.

    a single-table join index with a FK primary index

    reducing I/Os and message traffic because row redistribution is not required, since the following are hashed to the same AMP:

  • A single-table join index having a primary index based on the base table foreign key.
  • The table with the column set making up the foreign key.
  • defined with an outer join

  • Giving the same performance benefits as a single-table join index, for queries that reference only outer tables.
  • Preserving unmatched rows.
  • created using aggregates

    eliminating both the aggregate calculations and the join step for every query requiring the join and aggregate.

    For more information on the syntax, applications, restrictions, and benefits of join indexes, see SQL Data Definition Language.