Skew Factor vs Primary Indexes vs AMPs used 1

[terminalclick]

First time post, so please bare with me.

Please could someone explain the relationship between Skew Factor on Primary Indexes and which AMPs will be used?

Am I right in thinking the skew factor on a primary index is where there are large variances in the amount of different primary index values? (e.g. the value below for the number of A1 rows would cause bad skew)

Is it that one set of rows with the same value on the primary index is allocated to an AMP.e.g.

PRIVAL COUNT(*)
A1     20000    (AMP1)
C2     30       (AMP2)
EA     1        (AMP3)
...

Would it mean that the rows of pri value A1 would get allocated to AMP1, rows of pri value C2 would get allocated to AMP2 and the row of pri value EA would get allocated to AMP3 etc and once the last AMP is 'allocated' then it goes back to AMP1 in a roundabout way?

Thanks in advance.
Rich.

 

[dnoeth]

All rows in Teradata are distributed according to the hashvalue of the Primary Index columns. So all rows with the same PI value must be located on the same AMP. In your example one AMP will hold 20000 rows, another one 30 and a third one 1 row. Which AMP is responsible for which value is known only for a known system with a known number of AMPs.
When you submit following query, you'll see which value is located on which AMP on your system:
select
hashamp(hashbucket(hashrow(primary_index_columns))) as "AMP"
,count(*)
from
yourtable
group by 1
order by 2 desc;

To check for a special value:
select hashamp(hashbucket(hashrow(12,'a')));


For more details about hashing/data distribution in Teradata check the "Introduction to Teradata" manual.

Dieter