I have read that "For join performance, a NUPI can be a better choice than a UPI."
Someone please explain why is that? If it is a PI join then it is an same AMP operation how does NUPI will serve better than a UPI??
you don't give any context for "For join performance, a NUPI can be a better choice than a UPI." so it would require again the crystal ball to come up with a reply...
As ulrich says, you need the context because it really depends on your data and what you need to join on.
For example if your joins are most often on columns that will have duplicates then a NUPI using those column(s) may be a better choice than creating a unique PI using a column(s) that are not always used in the join.
As long as there is reasonable distribution with the nupi columns( not too many duplicates) then it will work well.