Secondary Index Usage Summary
All Teradata Database secondary indexes have the following properties:
Can enhance the speed of data retrieval.
Because of this, secondary indexes are most useful in decision support applications.
Do not affect base table data distribution.
Maximum of 32 secondary, hash, and join indexes defined per table. Each composite
NUSI that specifies an ORDER BY clause counts as 2 consecutive indexes in this calculation
(see “Importance of Consecutive Indexes for Value‑Ordered NUSIs” on page 434).
The limit of 32 indexes applies to any combination of secondary, hash, and join indexes
defined on a table, ranging from 0 secondary indexes and 32 join indexes, 11 hash
indexes, 11 join indexes, and 10 secondary indexes to 32 secondary indexes and 0 join
indexes.
This includes the system‑defined secondary indexes used to implement PRIMARY KEY and
UNIQUE constraints.
Can be composed of as many as 64 concatenated columns.
Can include columns defined with a UDT data type.
Cannot contain columns defined with XML, BLOB, CLOB, BLOB‑based UDT, CLOB‑based UDT,
XML‑based UDT, Period, or JSON data types.
You can define a NUSI on a single column with a geospatial data type, but you cannot
define a USI on a geospatial column.
You cannot define a composite NUSI that contains a geospatial column.
Cannot be defined on global temporary trace tables.
Can be created or dropped dynamically as data usage changes or if they are found not
to be useful for optimizing data retrieval performance.
Require additional disk space to store subtables.
Require additional I/Os on INSERTs, DELETEs, and possibly on UPDATEs and MERGEs.
Because of this, secondary indexes are not nearly as useful in OLTP applications as
they are in DSS applications.
Should not be defined on columns whose values change frequently.
Should not include columns that do not enhance selectivity.
Should not use composite secondary indexes when multiple single column indexes and
bit mapping might be used instead.
Composite secondary index is useful if it reduces the number of rows that must be
accessed.
The Optimizer does not use composite secondary indexes unless a WHERE clause condition
specifies explicit values for each column in the index.
Most efficient for selecting a small number of rows.
Can be unique or nonunique.
NUSIs can be hash-ordered or value-ordered.
Ordering for NUSIs defined with an ORDER BY clause is restricted to a single numeric
or DATE column of 4 of fewer bytes.
If they cover, or partially cover, a query, then they further improve their usefulness.