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

NUSI SUBTABLE :

            When a Non-Unique SI is defined on a table then Teradata will build the sub-table on each amp in the same fashion as that in USI. NUSI subtable points to rows of its own AMP ,In other word each NUSI sub-table will reflect and points to the those base rows in which it owns in Same AMP.

           Suppose we have an employee table(base table)on which we defined NUSI on the column FNAME.
  •       Immediately Teradata will create the sub-table on all AMPs.
  •       Each AMP will  hold the secondary index values for their rows in the base table only.In our examples,each AMP holds the FNAME column for all employee rows in the base table on their AMP(AMP local).
  •        Each AMP will find the FNAME will have the base table row ID so the AMP can retrieve it quickly if needed.if an amp contains duplicate first names,only one sub-table row for that name is built with multiple base row _id’s.the above example for FNAME=’john’the sub-table holds multiple base Row ID for this value.
Suppose on the below example we make query 

Select * from employee table where FNAME=’john’ ;

then Teradata will search for the NUSI rows in sub-table present all the AMPs , and there by will search in respective tables in same AMP with condition  FNAME=’john’ on table employee .



NUSI Requests Are Not Hashed.

NUSI is all AMP and same AMP.