Primary Indexes

                                          Primary Indexes

Indexing is one of the most important features of the Teradata RDBMS. In the Teradata RDBMS, an index is used to define row uniqueness and retrieve data rows, it also can be used to enforce the primary key and unique constraints for a table. 

1. Primary index is a mandatory index.
2. Created at the time of Table creation only.
3. Only one PI for one Table, But PI can be composite upto 64 columns.
4. If u don't create Index on the table system automatically takes first column as PI.
5. It is a physical mechanism to assign a row to AMP.
6. It effects on Data Distribution.
7. UPI(Unique Primary Index) takes only Unique Values.
8. NUPI(Non Unique Primary Index) takes Duplicates and Null Values.
9. PPI for range based queries.
10. PI applys physical mechanism at the time of storage and retrieval.

Note: PI operation is one AMP operation and effects one or many ROWs.

Types Of Indexes

Unique Primary Index (UPI)

Non-Unique Primary Index (NUPI)

Partition Primay Index(PPI)

Unique Secondary Index (USI)

Non-Unique Secondary Index (NUSI)

Value Ordered Secondary Index(VOSI)

Join Index(JI)

Unique Primary Index:

1. It takes only unique values and one null value.
2. It distributes data across all AMP's uniformly (or) Randomly (or) evenly , so this distribution is called Uniform Distribution (or) Even distribution.
3. One AMP operation and operates only one row

Non Unique Primary Index:

1. It takes duplicates and nulls.
2. It distributes the data across all AMP's ,it distributes duplicates into same AMP this distribution is less even distribution or uneven distribution.
3. This distribution can also be called as 'SKEWED DISTRIBUTION' and less uniform less is 'SKEWNESS'.
4. One AMP operation and effects many ROWS.

Partition Primary Index:

1. Partition primary index is physically splitting the table into series of sub tables.
2.  one subtable for every partitioning value.
3.  When a single row is accessed, it looks first at the partitioning value to determine the subtable, then at the primary index to calculate the rowhash for the row(s). 

For example, we have PPI on a MONTH Column, the rows of particular months are all sorted with in the same partition and whenever data is accessed for particular month, it will retrive the data in a faster way.

Note:It helps to avoid full table scans.