Secondary Indexes | SQL Fundamentals | Teradata Vantage - 17.10 - Secondary Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Secondary indexes are never required for database tables, but they can often improve system performance.

You create secondary indexes explicitly using the CREATE TABLE and CREATE INDEX statements. The database can implicitly create unique secondary indexes; for example, when you use a CREATE TABLE statement that specifies a primary index, the database implicitly creates unique secondary indexes on column sets that you specify using PRIMARY KEY or UNIQUE constraints.

Creating a secondary index causes the database to build a separate internal subtable to contain the index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated.

Nonunique secondary indexes (NUSIs) can be specified as either hash-ordered or value-ordered. Value-ordered NUSIs are limited to a single numeric-valued (including DATE) sort key whose size is four or fewer bytes.

Secondary index subtables are also duplicated whenever a table is defined with FALLBACK.

After the table is created and usage patterns have developed, additional secondary indexes can be defined with the CREATE INDEX statement.