Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-25-2011
04:47 AM

03-25-2011
04:47 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-26-2011
09:21 AM

03-26-2011
09:21 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-27-2011
09:08 PM

03-27-2011
09:08 PM

Thanks Dieter for explanation.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-26-2016
03:45 AM

12-26-2016
03:45 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-26-2016
04:45 AM

12-26-2016
04:45 AM

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.