The typical use of a hash-ordered NUSI is with an equality condition on the secondary index column set. The specified secondary key value is hashed, and then each NUSI subtable is probed for rows having the same row hash. For every matching NUSI entry, the corresponding rowIDs are used to access the base rows on the same AMP. Because the NUSI rows are stored in row hash order, searching the NUSI subtable for a row hash value is efficient.
Value-ordered NUSIs are useful for processing range conditions and conditions with either an equality or inequality on the ordering column of the secondary index.
The Optimizer can select hash-ordered NUSIs to access rows for range conditions using a full-table scan of the NUSI subtable to find the matching secondary key values.
With a value-ordered NUSI, only a portion of the index subtable for a given range of key values must be searched.
Example
The following example shows a value-ordered NUSI (defined by an ORDER BY clause that specifies the VALUES keyword option on o_orderdate) and a query that may be solved more efficiently if the specified value-ordered NUSI were selected by the Optimizer to access the requested rows.
CREATE INDEX Idx_Date (o_orderdate) ORDER BY VALUES (o_orderdate) ON Orders; SELECT * FROM Orders WHERE o_orderdate BETWEEN DATE '2005-10-01' AND DATE '2005-10-07';