I have a question. Why can't a NUSI be hash distributed to the subtables in different amps just as it is done in case of a USI and in that case the non unique NUSI values can be placed in the same AMP's sub table where it is hashed to with mutiple base table Row IDs for that NUSI value . In this case the retrieval process should be less than an all Amp operation.
You might have a column with a skewed distribution, e.g. the same default value or NULL in 20% of the rows, which would result in a heavily skewed subtable.
And NUSI maintenance would be much higher because of the neccessary inter-AMP communication.
It is possible to create an index that is hash distributed as you describe. Use a single table join index with a primary index of the fields you want it hash distributed on. You can have ROWIDs in the index so it can work like a NUSI or you can cover the queries that you will be running with this access path.