Teradata-Secondary Index

Secondary Indexes provide an alternate path to the data, and should be used on queries that run thousands of times.

Teradata Secondary Index

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:

  1. Secondary Index Value 
  2. Secondary Index Row ID 
  3. Primary Index Row ID

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.


Popular posts from this blog

SQL for Quantile Function in Teradata

3 Uses of SAMPLE function in Teradata

All You Need NULL Value Functions