Typical Uses of Value-Ordered and Hash-Ordered NUSIs
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 particular row hash value is very efficient.
Value-ordered NUSIs, on the other hand, are useful for processing range conditions and conditions with either an equality or inequality on the secondary index column set.
Although hash-ordered NUSIs can be selected by the Optimizer to access rows for range conditions, a far more common response is to specify a full‑table scan of the NUSI subtable to find the matching secondary key values. Therefore, depending on the size of the NUSI subtable and the number of qualified rows, this might not be very efficient.
By sorting the NUSI rows by data value, it is possible to search only a portion of the index subtable for a given range of key values. The Optimizer must still estimate the selectivity of a NUSI to be high for it to cost less than a full‑table scan. The major advantage of a value‑ordered NUSI is in the performance of range queries.
The following example illustrates a value-ordered NUSI (defined by an ORDER BY clause that specifies the VALUES keyword option on o_orderdate) and a query that would probably 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)
BETWEEN DATE ‘2005-10-01’
AND DATE ‘2005-10-07’;