Unique Primary Index Teradata (UPI)

       UNIQUE PRIMARY INDEX (UPI) is unique and cannot have any duplicate column values. If you insert a row with same primary index value that is already in the table, the row will be rejected as a UPI enforces UNIQUENESS for a column. A UNIQUE PRIMARY INDEX (UPI) will always spread the rows of the table evenly amongst the AMP’S UPI access is always a one – AMP operation.

        We have selected EMP-NO to be our PRIMARY INDEX because we have declared EMP-NO to be a UNIQUE PRIMARY INDEX there can be no duplicate employee numbers in the table.

UPI is ONE AMP Operation . It is suggestible to UPI set on correct column in the Teradata table. 

Example for UPI :

EMP-NO	DEP-NO	FIRST NAME	LAST NAME	SALARY
1111	1000	Smith	Jones	50000
3333	3000	Johnson	Stewart	45000
4444	4000	Peter	Jones	85000
6666	6000	Sandy	Vatish	96000
8888	8000	Matt	Stewart	52000
9999	9000	Leonia	Lacy	63000

   

TERADATA ENFORCE UNIQUENESS ON AN UPI

Data Distribution or storing a record in Unique Primary Index(UPI):

{ Please refer Primary Index Page for Diagram}

Step 1: After record reaches the PE , Row hash is generated for the given row , In this case Uniqueness value is 0
Step 2 : The Teradata searches the Amp value in the hash map which is present in BYNET
Step 3 : After searching for the right AMP , the row is moved to the respective Vdisk which is under the AMP provided in step 2

Data Retrieval Unique Primary Index (UPI) :

Step 1: When Row came to PE , the respective Hash value is calculated
Step 2 : Based on the Row hash value the repective AMP is taken
Step 3 : The Row is searched in the Amp using the Physical row id provided and given back to PE.

Syntax for UPI Creation :

CREATE TABLE TABLENAME 
( Col1 datatype ,
Col2 datatype )
Unique Primary Index ( Col1);

      

 Sample UPI Creation with example :                   

CREATE TABLE sample_1.

(COL-A INT, COL-B INT, COL-C INT)

UNIQUE PRIMARY INDEX (COL-B);