Teradata runs extremely well without secondary indexes, but since secondary indexes use up space and overhead, they should only be used on "KNOWN QUERIES" or queries that are run over and over again. Once you know the data warehouse, environment you can create secondary indexes to enhance its performance.
Whenever a secondary index is created, Teradata creates a secondary index subtable on each AMP. All secondary index subtables contain:
- Secondary Index Value
- Secondary Index Row ID
- Primary Index Row ID
Again, secondary indexes are two types:
A UNIQUE Secondary Index (USI) will improve data retrieval and can also be used to enforce uniqueness on a primary key. Typically, only two AMPs are used on a Unique Secondary Index (USI) access.
A Non-Unique Secondary Index (NUSI) is AMP local and is an All AMP operation, but not a full table scan