how does we retrieve exact record in case of hash collission

Database
Enthusiast

how does we retrieve exact record in case of hash collission

Hi,

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

Thanks

4 REPLIES
Junior Supporter

Re: how does we retrieve exact record in case of hash collission

ROWID (unique) = ROWHASH + UNIQUE (32 bit)

jones, homes, whatever: Same ROWHASH, but different ROWID.

HTH.

Cheers.

Carlos.

Enthusiast

Re: how does we retrieve exact record in case of hash collission

Q. Are we storing the uniqueness value somewhere?

A. 'Yes'!

See: http://forums.teradata.com/forum/database/rowkey

Teradata Employee

Re: how does we retrieve exact record in case of hash collission

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.

Enthusiast

Re: how does we retrieve exact record in case of hash collission

thank you all for your clarification.