Typical Uses of Value-Ordered and Hash-Ordered NUSIs - Teradata VantageCloud Lake

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.

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