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);