difference of hashing mechanism in between UPI and NUPI

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

difference of hashing mechanism in between UPI and NUPI

Hi Everyone,

 

1) Is there any difference in hashing mechanism between UPI and NUPI while distributing table records in teradata amps?

 

Lets say we have two tables 

 

create Multiset table DB.emptest
(
emp_id integer,
emp_name varchar(20),
emp_add varchar(20)
)
NON Unique primary index (emp_id);

 

 

create Multiset table DB.emptest
(
emp_id integer,
emp_name varchar(20),
emp_add varchar(20)
)
Unique primary index (emp_id);

 

2) considering these two tables we will see difference in skewness as per UPI and NUPI but I would like to know how the performace will be differed ?

 

Thanks in advance,

Deborshi

2 REPLIES 2
Supporter

Re: difference of hashing mechanism in between UPI and NUPI

re 1) USI and NUSI hash the same way. Join between your two tables will be without redistribution.

re 2) If same data is loaded in both tables (emp_id is unique), they will be identically distributed. Performance of the USI table might be better as more aggressive join plans may get selected (you provide optimizer with one more piece of information: emp_id is Unique).

Cheers, Frank

Teradata Frank, Certified Master
Highlighted
Enthusiast

Re: difference of hashing mechanism in between UPI and NUPI

THANK YOU FGROENEN.