A table can contain only one primary index. More often, you will come across scenarios where the table contains other columns, using which the data is frequently accessed. Teradata will perform full table scan for those queries. Secondary indexes 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)
Unique Secondary Index (USI)
A Unique Secondary Index allows only unique values for the columns defined as USI. Accessing the row by USI is a two amp operation.
Create Unique Secondary Index
The following example creates USI on EmployeeNo column of employee table.
CREATE UNIQUE INDEX(EmployeeNo) on employee;
Non Unique Secondary Index (NUSI)
A Non-Unique Secondary Index allows duplicate values for the columns defined as NUSI. Accessing the row by NUSI is all-amp operation.
Create Non Unique Secondary Index
The following example creates NUSI on FirstName column of employee table.
CREATE INDEX(FirstName) on Employee;