Data distribution on AMPs with UPI

Database

Data distribution on AMPs with UPI

Hi

I am using Teradata V2R6.
I have a table with 1000 records and the UPI defined for the table.
I wanted to see the data distribution for this table among all the AMPs.
The database has 46 AMPs.
I executed the query:

SELECT
hashamp(Hashbucket (Hashrow(fieldname))) AMP_NO
,Count(*) AS No_Of_Rows
From dbname.tbname
GROUP BY 1
ORDER BY 2
;

It gave me data distribution done among all the AMPs.
The Minimum records on one of the AMP is 10 and maximum record is 35.
I was expecting that there should have been an even distribution among all the AMPs.

Can any one explain why is there an uneven distribution of data even when i have UPI defined for the table?

thanks.
4 REPLIES
Senior Apprentice

Re: Data distribution on AMPs with UPI

When you roll a dice 60 times do you expect that each number shows up exactly 10 times?

A UPI is just guaranteeing the uniqueness of the data but not the uniqueness of distribution. Data is distributed based on a hash value, but not round robin.

Add more rows and the table is approaching an even distribution.
This is also true for NUPIs (within limits).

Dieter

Re: Data distribution on AMPs with UPI

Thanks Dieter for explanation.
sri
Enthusiast

Re: Data distribution on AMPs with UPI

Thank dnoeth for your explanation. But Teradata says that UPI provide even dsitribution of rows. I agree with your explanation but there should be a way how rows will be distribute among AMPs with UPI. So if I insert 100 rows into 10AMPs...what are the factors that affect even distribution and how to find number of rows present in each AMP along with AMP number?

 

Thanks in advance.

Sri.

Senior Apprentice

Re: Data distribution on AMPs with UPI

You shouldn't care about small tables, a UPI approaches an even distribution when the number of rows increases. 

 

And the query to find the number of rows/AMP is shown in the 1st post.