Secondary indexes are never required for Teradata Database tables, but they can often improve system performance.
You create secondary indexes explicitly using the CREATE TABLE and CREATE INDEX statements. Teradata Database can implicitly create unique secondary indexes; for example, when you use a CREATE TABLE statement that specifies a primary index, Teradata Database implicitly creates unique secondary indexes on column sets that you specify using PRIMARY KEY or UNIQUE constraints.
Creating a secondary index causes Teradata 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.