Chapter 9 Secondary Indexes - Teradata Database

Teradata Database Design

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

This chapter describes Teradata Database 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 Teradata 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, Teradata Database is also highly optimized to perform full‑table scans in parallel. Because of the strength of full‑table scan optimization in Teradata Database, there is little reason to be heavy-handed about assigning multiple secondary indexes to a table.

You can create secondary indexes when you create the table (see “CREATE TABLE” in SQL Data Definition Language), or you can add them later using the CREATE INDEX statement (see “CREATE INDEX” in SQL Data Definition Language). Unlike primary indexes, secondary indexes can be dropped and created at will without altering the table they index.

You can define a secondary index on a row-level security-protected column.

Note that Teradata does not use the term secondary index in the same way it is commonly used by other relational DBMS vendors to describe a non-clustered index in an indexing system based on B+ trees.

Data access using a secondary index varies depending on whether the index is unique or nonunique.

For more information about how Teradata Database accesses data using a unique secondary index, see “USI Access” on page 406.

For more information about how Teradata Database accesses data using a non-unique secondary index, see “NUSI Access and Performance” on page 419.

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

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 for details on how to do this.