primary index hash value

Database
Enthusiast

primary index hash value

if 123 is a value of an unique primary index column of a particular table A,it will generate a particular hash value via hashing algorithm...now suppose a second Table B also has 123 in its unique primary index column......this will also generate the same hash value and will be there in the same AMP.in this case how the AMP will know that which row corresponds to which table.

Thanks
Sreehari
6 REPLIES
Enthusiast

Re: primary index hash value

Kind of wizard, but this is the thing which makes Teradata data distribution so optimal...
123 Value will be on same AMP but in different data block(mostly different cylinder also)...
Same AMP thing will make it useful for join purpose i.e when these 2 tables will be joined on this column no data is needed to be redistributed as both are on same AMP.....can be processed locally...

Whenever a new row is inserted a 3 part message is generated Table ID+row hash+PI value...
so a row will definitely go to his companion only, by using Table ID....
For more you can Teradata manuals -Physical Implementation.......

Regards,
Prakhar
Enthusiast

Re: primary index hash value

Hi Thanks for the info.........
my understanding till now was that....all rows in an AMP will be identified by 32bit hash value+32 bit uniqueness vaue.....
Enthusiast

Re: primary index hash value

Your understanding is still correct for non-PPI tables. With PPI, the row identifier is effectively extended by 16 bits to include the partition identifier.

Re: primary index hash value

how to get uniques ness value for row in teradata for Hashing?

Enthusiast

Re: primary index hash value

SELECT HASHROW(<<COLUMNLIST>>), COUNT(*)
FROM <<TABLENAME>>

Teradata Employee

Re: primary index hash value

HashRow will not give you the "row uniqueness" portion of the row-id.  The SQL that Qaisar posted merely counts the rows currently in the table for a given row hash value and does not give you a row uniqueness value for each individual row.  The only way that I know of to get the uniqueness value for a particular row as Rajgopal asked is to use one of the system console utilities such as Filer to perform a dump of the physical row.