Interview Questions   Tutorials   Discussions   Programs   

Teradata - What is Primary index ?

asked vishnoiprem August 17, 2014 04:18 AM  

What is Primary index ?


1 Answers

answered By vishnoiprem   0  
Teradata takes each table and spreads the table rows across the AMPs. When the table
needs to be read, each AMP has to read only their portion of the table. If the AMPs start
reading at the same time and there are an equal amount of rows on each AMP, then
parallel processing works brilliantly. Alone an AMP can do so little, but the AMPs
working together can accomplish the incredible. This brilliant feat begins with the
Primary Index.
Each table in Teradata is required to have a Primary Index. The biggest key to a great
Teradata Database Design begins with choosing the correct Primary Index. The Primary
Index will determine on which AMP a row will reside. Because this concept is extremely
important, let me state again that the Primary Index is the only thing that will determine
on which AMP a row will reside.

Many people new to Teradata assume that the most important concept concerning the
Primary Index is data distribution. INCORRECT! The Primary Index does determine
data distribution, but even more importantly, the Primary Index provides the fastest
physical path to retrieving data. The Primary Index also plays an incredibly important
role in how joins are performed. Remember these three important concepts of the
Primary Index and you are well on your way to a great Physical Database Design.

The Primary Index plays 3 roles:
• Data Distribution
• Fastest Way to Retrieve Data
• Incredibly important for Joins

Because the Primary Index is so important it should NOT be frequently changed. It
should also be a column that is frequently used in row selection. This means that the
SQL should utilize this column often in the WHERE clause. Although you might be
tempted to pick a column with poor distribution you should not.
This can cause errors
stating “Database FULL Conditions”.

   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!