single column NUSI value or hash order

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

single column NUSI value or hash order

Hi there,

 

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)?

5 REPLIES
Teradata Employee

Re: single column NUSI value or hash order

Hi Ulli,

 

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.

Enthusiast

Re: single column NUSI value or hash order

Hi Waldar,

 

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?

 

Teradata Employee

Re: single column NUSI value or hash order

Hi Ulli,

 

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.

 

Teradata Employee

Re: single column NUSI value or hash order

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).

Enthusiast

Re: single column NUSI value or hash order

Todd / Waldar - thanks for the replies.