Secondary Index that is created on Columns having Unique set of Values is called Unique Secondary Index.When we define  a Unique SI on the table, then the teradata will immediately create a USI subtable in each AMP for that particular table.

 Example :

           Suppose we have employee table(base table)having attributes DEPT , FNAME,LNAME and Pan_number .We defined USI on the column Pan_number .The SI subtable created on each AMP which holds information about the SI column and corresponding base row id(base table row_id)which is the row id of the actual employee table. The steps involved to load this  subtable is as follows:-
  •     Teradata creates sub-table on all AMPs.
  •     Hashing will be performed on Secondary Index column (Pan_number) and based on that hashed value it checks the hash map for the AMP number which will hold this USI value in its sub-table.


  •       Once it finds the respective AMP Number ,the SI value along with the two or more attributes (secondary index row id and base table row id) will be stored in the sub table of that AMP.
Teradata retrieval of USI query :

Suppose on the above example we make a query.

Select * from employee where Pan_number =”STMPY7899”;

When a TD optimizer  find a USI in where clause it knows that it’s a 2 amp operation and also only one row will be returned. so the step its performs for  retrieval is as follows ...
  •        It will hash the value of SI "STMPY7899" by hashing algorithm and found the hash value for it.
  •        After getting the hash value it checks the respective AMP number in Hash Map . 
  •        It goes to respective AMP to find the physical rowid ( Base rowID)in the sub-table.
  •         This base row id will be sent back to optimizer by BYNET
  •         Now optimizer sent back this ROW ID again and fetch the resultant row from the base table for which Pan_number = "STMPY7899".
      As we have seen that Teradata system requires 2amp to each the answer row that’s why we called USI operation as the 2 amp operations.Even if SI row resides in the same amp in which base row reside,still after getting base row id from the subtable it will sent back to optimizer so that it start search again based on that base row id.so it’s always called as the 2 amp operation.

 Special Note :

 Every Table has its own USI subtable and NUSI subtable independently if SI is created on it.