Primary Index in Teradata

#Primary Index in Teradata

Primary index is created while creating Table. We no need to worry about definition. If we forget to mention Primary index, Teradata will create it automatically.

Create table samples.orders
(Order_no INT, Amount DEC (5,2), Total INTEGER, Item_name VARCHAR(10)
Unique Primary Index(Order_No);

How Teradata selects UPI?

It selects first column in the Table as UPI (Unique Primary Index). By default Teradata create UPI.

NUPI - Is also called non-unique primary index. During the table definition we can give NUPI. Especially we are loading data into Staging-tables from mainframe or other server.

The disadvantage of NUPI is all the duplicate rows, grouped together in same AMP. It causes for skewing. But this kind of skewing is acceptable.

Where we need to give PI in SELECT statement.

For efficient performance, we need to give PI in where clause.

SELECT * from Samples.order
where Order_No = 10005;

Keep reading for more on Teradata BI.