Using Indexes to Enhance Performance

Using Indexes to Enhance Performance - Teradata Database

Teradata Database Design

Product

Teradata Database

Release Number

15.10

Language

English (United States)

Last Update

2018-10-06

Product Category

Software

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.