Secondary Index other sort of faster retrieval of query results a part from Primary Index , however Secondary Index (S I ) is always slower than Primary Index .
Secondary Index is an index that is created on set of columns which are not in Primary Index and are helpful in faster retrieval of the rows from database. In case user want to fetch the rows based on columns which are in where clause and are not part of row distribution, then we will use secondary index.
  Secondary Indexes occupy permanent space for maintenance and existence. SI can be created even after table is populated with the data, unlike Primary Index which is created only at the time of creation of table .we can create and drop secondary index at any time.

Whenever we create a SI on the table, Teradata will create a sub-table on all AMPS.  This sub-table contains three columns given below

        1. Row id for Row Value (This is the hash value of SI value)
        2. Row value 
        3. Base Row ID (This is the actual base row id)

Sub-table Mechanism:




Every row will have Row ID computed on basis of Primary Index .
Syntax of the Secondary Index:-

CREATE INDEX(COLUMN/COLUMNS)ON<dbname>.<table name>

These are two types:-
•        Unique secondary index          (USI)
•        Non Unique secondary index   (NUSI)


Diagram of secondary Index :





Syntax of Secondary Index :

Example of Secondary Index :


SECONDARY INDEX Limitations:-

·        We can have up to 32 SI for  a table
·        USI subtables are hash distributed
·        NUSI subtables are AMP local
·        USI queries are two amp operations
·        NUSI queries are all amp operations, but not full table scans.

·        Always collect statistics on all NUSI indexes.