Saturday, 14 March 2015

Teradata-Secondary Index

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

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

No comments:

Post a Comment