General (theoretical) question; for queries with an equality-condition on just one secondary index column, would a value-ordered NUSI or a hash-ordered NUSI perform better (and why)?
For an equality join, hash ordered NUSI would perform better.
Value Ordered NUSI are mostly usefull for filter with between conditions.
But also keep in mind that NUSI are rarelly usefull, they are extremly specific.
Ok thanks, but why is that?
I would say that directly looking up the (single) where-clause-value in a value-ordered NUSI would be quicker than if an extra hashing-step for a hash-ordered NUSI would be involved?
I tried to find a performance comparaison between hash ordered and value ordered index, but I didn't find any.
I think the main thing that will make you build more hash ordered index are the limitations of value ordered NUSI : only a single numeric (4-byte) or date column.
The lookup performance will not be measurably different for value ordered or hashed NUSI for an equality condition.
The value ordered NUSI costs more to maintain. Plus, as noted above, has limitations on the data types supported.
Value ordered NUSI should be considered when there are very selective range conditions on a supported type (eg small date ranges in a large table).