The distribution of the subtable rows for a join index across the AMPs is controlled by its NUPI. By default, join index subtable rows are sorted locally on each AMP by the row hash value of the NUPI column set. You can also specify that rows be stored in value-order.
Value Ordering and Range Queries
You can specify value ordering by means of the optional ORDER BY clause in the join index definition. Sorting a join index by values, as opposed to row hash values, is especially useful for range queries involving the sort key. Value ordering is limited to a single numeric or DATE columns with a size of 4 bytes or less.
For example, the following join index rows are hash-distributed using c_name and are value-ordered on each AMP using c_custkey as the sort key.
CREATE JOIN INDEX OrdCustIdx AS SELECT (o_custkey ,c_name) , (o_status ,o_date ,o_comment) FROM Orders LEFT JOIN Customer ON o_custkey = c_custkey ORDER BY o_custkey PRIMARY INDEX (c_name);