Using Indexes to Enhance Performance - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Using Indexes to Enhance Performance

The following table summarizes how the Optimizer can use indexes to enhance query performance.


Index Type

Standard Use

Unique primary

Ensure the fastest access to single rows

Nonunique primary

  • To perform a single-AMP row selection or join process
  • To avoid sorting or redistributing rows
  • Primary AMP

    Provides the advantages of NoPI on column-partitioned tables but adds single-AMP access, local access when joining on the primary AMP index columns, and improved performance for aggregation when grouping by the primary AMP index columns. A primary AMP index is a nonunique index.

    Unique primary to match values in one table with index values in another

    Ensure optimal join performance.

    Unique secondary

    Process requests that specify equality constraints

    Nonunique secondary

  • Provides access using information that may be more readily available than a UPI value, such as an employee last name, compared with an employee number
  • Provides efficient access using one NUSI when the number of rows per value in the table is relatively small
  • Provides bitmapping using multiple NUSIs (where each is not very selective but in combination they are)
  • Composite index only

    Optimal processing of requests that employ equality constraints for all fields that comprise the index.

    Multiple NUSIs with bitmapping

    Process requests when equality or range constraints involving multiple NUSIs are applied to very large tables.

    For smaller tables, the Optimizer uses the index estimated to have the fewest rows per index value.

    Using appropriate secondary indexes for the table can increase retrieval performance, but the tradeoff is that update performance can decrease because of the need to update secondary index subtables.