Data Distribution Using Primary Index

[terminalclick]

Hi,

I think I'm correct in saying that the Primary Index is set against a table to effect data distribution across AMPs. So, in effect, a good primary index is a column containing unique data.
Am I therefore correct in thinking that if a 'serial' column is added to a table to just count from 1 upwards for each row, this would be ideal for a primary index? (i.e. it will mean the rows are evenly distributed across AMPs even though the column isn't used for actual joining)
(Please correct me if I misunderstand).

Thanks in advance, Rich.

 

[AnilThapak]

A serial column will lead to evenly distribution of data but If this column is not used for actual joining or access the row then it will lead to performance penalty because reading a row will require fulltable scan.
Joining becomes more efficient if the joined columns are also the primary index. In this case all the row having same index value remain on same amp makes processing faster.

So a ideal column for PI index is one who is unique and heavily used for processing.