A Secondary Index provides an alternate path to the data. They can greatly enhance performance, but there is a price to pay. All secondary indexes create a secondary index subtable. The price is additional space and overhead maintenance. You must be able to weigh the increased speed with the space and overhead maintenance. If the secondary index provides enhanced performance you will probably want it, but if it does not, then drop it. This is why the most important metric for secondary indexes is Value Access Frequency. We will discuss all options for the USI, and then the NUSI. USI Considerations
There are three reasons to create a Unique Secondary Index on a column: You want to ensure that a columns values are unique. You have a column with a high value access frequency and you want users to get to the data quickly. You have a SET table that has a NUPI. Many of the NUPI values have a large amount of Typical Rows Per Column or there is a great deal of collisions or synonyms. Teradata will have to do Duplicate Row Checking to ensure no duplicate rows exist. This process can be time intensive. Placing an USI on another column eliminates this extra checking.
USI to Enforce Uniqueness If a column has been designated as having no duplicates, such as the Primary Key, and it is important to enforce the uniqueness, the recommendation would be to create a Unique Secondary Index (USI). Teradata will ensure that no duplicate values are entered for all Unique Secondary Index columns. If a user tries to enter a value already in the table the transaction will be aborted and the user is sent a Duplicate Value message. USI for High Value Access Frequency If a column has a high Value Access Frequency and is unique, then it is the perfect USI. A Unique Secondary Index will speed up queries dramatically. A USI is always a two- AMP operation. When using an USI in the WHERE clause of a query, the answer set will never be more than one row. This is because the row you are after is unique, so there can only be one. An USI is an excellent choice if conditions permit.