Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?

Comment 1) Mulit Load has support for Non-Unique Secondary Indexes (NUSIs) since NUSIs are AMP Local. They have sub table within

base table. It has no hash bucket to redirect to another amp, everything points to their own amp (AMP Local)

If you go with USI, it has hash bucket, which might redirect to another amp, more possibilities for full table scan.

Motive for avoiding Indexes (other than NUSI): Avoid Full table scan.

Comment 2) Like FastLoad, MultiLoad does not support Unique Secondary Indexes (USIs). But unlike FastLoad, it does support the use of Non-Unique Secondary Indexes (NUSIs) because the index sub table row is on the same AMP as the data row. MultiLoad uses every AMP independently and in parallel. If two AMPs must communicate, they are not independent. Therefore, a NUSI (same AMP) is fine, but a USI (different AMP) is not.

Comment 3) whenever we define a SI an SI sub table is created in each AMP. For USI they go for a hash distribution, and hence the actual data row pointed by the USI sub table rows in one AMP may not be in the same AMP as the sub table..so the AMPs have to communicate, which is not supported by Multi load. For NUSI the sub table will store references of only those actual data rows who exist in the same AMP as the sub table..they all point to the data in their own AMP..hence AMPs don't need to communicate here.

Thus the AMPs work in parallel with NUSI and hence Mload supports that.