Sorting a join index by data values, as opposed to row hash values, is especially useful for range queries that involve the sort key. Therefore, defining a join index designed to support range queries using row-partitioning can significantly facilitate the performance of range queries.
Similarly, you may get better performance by designing the join index without row partitioning, but using a value-ordered NUSI.
Limitations:
- Value ordering is limited to a single numeric or DATE column with a maximum length of four bytes.
- The column you specify in the ORDER BY clause must be drawn from the set of fixed columns. You cannot order by a column from the set of repeating columns.
In the following example, the join index rows are hash-distributed across the AMPs on the primary index, c_name. Once assigned to an AMP, the join index rows are value-ordered using c_custkey as the sort key.
CREATE JOIN INDEX ord_cust_idx 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);