There is a table as given below and some data.NUPI is on lastname column.
hash collission has happened for persons whose lastname are jones and homes.
When the below query is fired
select * from table_name where lastname='homes';
how does teradata ensures that it is fecthing the correct record as there are same
rowhash value and they are placed in same AMP.are we storing the uniqueness value somewhere?
rowid lastname firstname empno deptno sal
0001,1 jones a 1 10 2000
0001,2 jones b 2 10 2100
0001,3 homes c 3 10 2200---->hash collission occured
0001,4 jones d 4 20 2400
0011,1 baker e 5 30 2500
ROWID (unique) = ROWHASH + UNIQUE (32 bit)
jones, homes, whatever: Same ROWHASH, but different ROWID.
Q. Are we storing the uniqueness value somewhere?
Teradata uses rowhash as a general filter but then always compares teh actual value in the row before returning it.
While it is correct that a uniqueness value is stored, this is for identifying a row uniquely in the system despite hash collisions and data repetition. We don't know what uniqueid we are looking for when searching by value so only the rowhash and value are used to qualify the row.