15.00 - Using Indexes to Enhance Performance - Teradata Database

Teradata Database Design

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

Using Indexes to Enhance Performance

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

 

Index Type

Standard Use

Primary

Satisfy an equality on an IN condition in a join

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
  • Unique secondary

    Process requests that specify equality constraints

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

    Ensure optimal join performance.

    Composite index only

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

    Bitmapped NUSI

    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.