Secondary Indexes | Database Design | Teradata Database - Secondary Indexes - Teradata Vantage - Database Engine 20

Database Design

Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Database Engine 20
Teradata Vantage
Release Number
20.00
Publication Date
June 2025
Content Type
Administration
User Guide
Publication ID
B035-1094-200K
ft:locale
en-US
ft:lastEdition
2026-11-02
dita:mapPath
vkh1747160146537.ditamap
dita:ditavalPath
jlp1749501702948.ditaval
ft:sourceId
dita
dita:id
zqc1472244571611
lifecycle
lifecycle
Product Category
Teradata Vantageā„¢

This section describes Unique Secondary Indexes (USIs) and Nonunique Secondary Indexes (NUSIs).

When column demographics suggest their usefulness, the Optimizer selects secondary indexes to provide faster single row or set selection, depending on whether the index is a USI or a NUSI, respectively.

A secondary index is never required for database tables, but they can often improve system performance, particularly in decision support environments.

While secondary indexes are useful for optimizing repetitive and standardized queries, the system is also highly optimized to perform full-table scans in parallel. Because of the strength of full-table scan optimization, there is little reason to be heavy-handed about assigning multiple secondary indexes to a table.

You can either create secondary indexes when you create the table or you can add them later using the CREATE INDEX statement (see SQL Data Definition Language Syntax and Examples, B035-1144). Unlike primary indexes, secondary indexes can be dropped and created at will.

For more information about how the system accesses data using a unique secondary index, see USI Access.

For more information about how the system accesses data using a nonunique secondary index, see NUSI Access and Performance.

For design issues related to secondary index support for tactical queries, see Two-AMP Operations: USI Access and Tactical Queries and NUSI Access and Tactical Queries.

As is true for other types of indexes, you should keep the statistics on your secondary indexes as current as possible.

The recommended practice for recollecting statistics is to set appropriate thresholds for recollection using the THRESHOLD options of the COLLECT STATISTICS statement. See "COLLECT STATISTICS" in SQL Data Definition Language Syntax and Examples, B035-1144 for details on how to do this.