The Teradata USI Compendium

Roland Wenzlofsky

May 13, 2014

minutes reading time

The Secondary Index in Teradata provides an alternate access path to the data records to reduce the disk IOs while retrieving the data.

While B+ Tree Indexes are commonly used in database systems, Teradata uses a different approach.

Teradata distinguishes between unique secondary indexes (USI) and non-unique secondary indexes (NUSI). Although the naming may seduce, assuming they are technically the same, they are not. This article is related to USI.

Unique secondary indexes are based on the same hashing algorithm Teradata applies to distribute data evenly across all disks. Technically, unique secondary indexes are tables like any other table. However, they are so-called sub-tables, meaning they do not appear as a real object in any dictionary table (in contrast to join indexes).

The base table data is hashed by the primary index column(s). In contrast, the secondary index rows are hashed by another column or combination of columns, which presents the additional data path.

Each USI row contains the ROWID of the base table row, making it possible to look up the base table row directly.

Teradata USI access is usually 2-AMP access: At first, the secondary index row is looked up. This row contains the ROWID of the base table, which is used to lookup the base table row. Sometimes, if lucky, the USI row and the base table row are located at the same AMP, making the retrieval of the base table row AMP-local:


Like a unique primary index, the unique secondary index distributes evenly across the AMPs, and there is no need to take care of data skewing.

As always, Teradata needs recent statistics to consider index usage. USI has to be highly selective. Otherwise, Teradata probably decides not to use it when building the execution plan. It is stated that if more than 10% of the table rows were selected from the USI, it would not be used. 10% is only a rule of thumb.

The usual approach is to create the USI you would like to be used and check if it is indeed used (EXPLAIN statement). If used, keep it; otherwise, drop it (remember, unique secondary indexes are physical tables occupying disk space).

Information about the Teradata USI you will find nowhere else:

The detailed data retrieval process

  • The Parsing Engine is consulting the hash map and locates the AMP carrying the USI row.
  • The AMP containing the USI row finds the USI data block via the Master Index & the Cylinder Index.
  • The USI data block is moved into the FSG cache.
  • The AMP does an in-memory binary search of the data block and locates the USI row.
  • The designated row carries the ROWID of the base table. The Parsing Engine uses this ROWID to look up the base table AMP and instructs it to retrieve the base table row.
  • The base table AMP locates the data block via Master Index & Cylinder Index lookup.
  • The base table AMP moves the data block into the FSG cache.
  • The base table row is located with an in-memory binary search on the base table block.
teradata usi

See also:
The Secondary Index in Teradata – The NUSI compendium
The Teradata Join Index Guide – We leave no questions unanswered!
The Teradata Partitioned Primary Index (PPI) Guide
Watch the Teradata Indexing Video Course

  • Hi Mayank,

    While the PI is the way Teradata distributes the records across all AMPs, the USI is working exactly the same way. Hence, you can imagine the USI as being the same table or some columns of the same table being hashed by a different PI. If the USI is covering all columns a query needs, Teradata has not even to lookup the related base table record, otherwise, the ROWID of the base table record which is stored in the USI record will be used to find the related base table row.

  • Hi Roland,

    Thank You for all the articles on your blog. Kudos to you!

    “Exactly the same as a unique primary index, the unique secondary index distributes evenly across the AMPs and there is no need to take care of data skewing.”: I think that only the Primary Index is responsible for distributing the table records across AMPs. Please correct me if I am mistaken.


  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like