Indexing issue

[Souvik]

Think its a wee bit conceptual, but could anybody explain how exactly the the primary index, the secondary index and single table join index differ from each other in terms storage & retrieval of data with an example??

Thanks ever so much!!!

 

[Patten]

The primary index determines on which AMP your data will be stored. The index value goes through the hashing algoritm and results in a row hash. This row hash is looked up in the hash map and determines on which AMP your row will be stored. The same happens when you are trying to retrieve a row from a table: if your query contains a condition on the primary index, it is sent throug the hashing algorithm which results in a row hash etcetera.
In terms of storage there is no extra storage needed for primary indexes.

Secondary indexes do take up extra storage space since on creation there will be build subtables.
To retrieve a row via a secondary index there is an extra step to be done.
Again the value of the secondary index is sent through the hashing algorithm which results in a hash value. This hash value is compared to the secondary index subtable where is found on which amp the row is stored. Then in a second step this row is accessed on the calculated amp in step one.

This is a very short and simple explanation. There are much more things to consider, since there are differences between unique and non unique primary/secondary indexes etcetera. Maybe you can ask your local Teradata responsible for a course Teradata Basics in which this stuff will be covered thoroughly.