Think of scenarios where table contains columns other than the index, using which the data is frequently accessed. Teradata will perform full table scan for those queries. Secondary indexes are needed to resolve this issue.
Secondary indexes are an alternate path to access the data. There are some differences between the primary index and the secondary index.
- Secondary index is not involved in data distribution.
- Secondary index values are stored in sub tables. These tables are built in all AMPs.
- Secondary indexes are optional.
- They can be created during table creation or after a table is created.
- They occupy additional space since they build sub-table and they also require maintenance since the sub-tables need to be updated for each new row.
There are two types of secondary indexes:
| Unique Secondary Index (USI) | Non-Unique Secondary Index (NUSI) |
|---|---|
| A Unique Secondary Index allows only unique values for the columns defined as USI. Accessing the row by USI is a two amp operation. | A Non-Unique Secondary Index allows duplicate values for the columns defined as NUSI. Accessing the row by NUSI is all-amp operation. |
| CREATE UNIQUE INDEX(CustNo) on Customer; | CREATE INDEX(FirstName) on Customer; |