Interview Questions   Tutorials   Discussions   Programs   

Teradata - What is secondary index ?

asked vishnoiprem August 17, 2014 05:39 AM  

What is secondary index ?


1 Answers

answered By vishnoiprem   0  
A Secondary Index provides an alternate path to the data. They can greatly enhance
performance, but there is a price to pay. All secondary indexes create a secondary
index subtable. The price is additional space and overhead maintenance. You must be
able to weigh the increased speed with the space and overhead maintenance. If the
secondary index provides enhanced performance you will probably want it, but if it does
not, then drop it. This is why the most important metric for secondary indexes is Value
Access Frequency. We will discuss all options for the USI, and then the NUSI.
USI Considerations

There are three reasons to create a Unique Secondary Index on a column:
• You want to ensure that a column’s values are unique.
• You have a column with a high value access frequency and you want users to get
to the data quickly.
• You have a SET table that has a NUPI. Many of the NUPI values have a large
amount of “Typical Rows Per Column” or there is a great deal of collisions or
synonyms. Teradata will have to do Duplicate Row Checking to ensure no
duplicate rows exist. This process can be time intensive. Placing an USI on
another column eliminates this extra checking.

USI to Enforce Uniqueness
If a column has been designated as having no duplicates, such as the Primary Key, and it
is important to enforce the uniqueness, the recommendation would be to create a
Unique Secondary Index (USI). Teradata will ensure that no duplicate values are
entered for all Unique Secondary Index columns. If a user tries to enter a value already
in the table the transaction will be aborted and the user is sent a “Duplicate Value”
USI for High Value Access Frequency
If a column has a high Value Access Frequency and is unique, then it is the perfect USI.
A Unique Secondary Index will speed up queries dramatically. A USI is always a two-
AMP operation. When using an USI in the WHERE clause of a query, the answer set
will never be more than one row. This is because the row you are after is unique, so there
can only be one. An USI is an excellent choice if conditions permit.
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!