Uniqueness Value

Database
Enthusiast

Uniqueness Value

Every table has its own set of data blocks.

Rows of different tables never appear on the same data block.

This minimizes the number of collisions to a trivial level..

Collisions or NUPI duplicates happen when an AMP encounters existing rows with same Row Hash.

The system increments the uniqueness value by one for each successive table row having the same Row Hash.

Given the above statements:

Does the mechanism of incrementing uniqueness value if a table row has the same row hash with an exisitng one know that it is only checking for one table? Or is it implied because of the design that Teradata tables never appear on the same data block? 

If Teradata was designed in such a way where a table may share the same data block, when a new row from a different table is inserted to the same data block of an existing row and both are having the same Row Hash, is the new row going to have an incremented uniqueness value or will it have the same uniqueness value of the existing row?

What are the factors being considered by this mechanism which increments the uniqueness value?

Tags (1)
6 REPLIES
Senior Apprentice

Re: Uniqueness Value

You just have to add "and the same tableid" to the last statement.

Both rows from different tables would have the same uniqueness value/rowid: All rows within an AMP are logically sorted by tableid/hash value/uniqueness value.

The usage of the ROWID keyword outside of a CREATE JOIN INDEX has been disabled in V2R5, but you could still switch it on using ROWIDEnabled (internal field 58) in dbscontrol. I usually do that on my training system to show how it actually works.

Diueter 

Enthusiast

Re: Uniqueness Value

Hi Dieter,

  • Table1 has Col1 with 1 record value of 'Pat' (VARHCAR(5)) --- existing row
  • Table2 has Col1 with 1 record value of 'Pat' (VARCHAR(5)) --- new row to be added

So this means that if for example Teradata allows for two different tables to store rows on the same data block..

Table1 used DataBlock #000A to store 'Pat' -- internally, it has rowhash #0001 since this is 1st record of Table1, uniquevalue is 1

When 'Pat' value is inserted to Table2 in the same data block..

Table2 used DataBlock #000A to store 'Pat' -- internally, it also has rowhash #0001 even if this is 2nd record with the same the row hash in the Data Block #000A, uniquevalue will still be 1 because this is still 1st record of Table2.

Wouldn't it be an issue? This is already considered as a collision right?

So the mechanism should actually increment the uniquevalue to 2 for the 1st record of Table 2? Or still not?

Basically, we're just trying to understand more about this statement:

Rows of different tables never appear on the same data block.

This minimizes the number of collisions to a trivial level.

What collisions is it pertaining to when tables can share the same data block?

Thanks!

Pat

Senior Apprentice

Re: Uniqueness Value

Hi Pat,

of course you're right regarding collisions, but:

If you actually start storing records from different tables on the same datablock you have to add the tableid to each record, otherwise you couldn't distinguish between rows from table A and those from table B.

This would add 6 bytes to each record, that's why they don't do it.

Dieter

Enthusiast

Re: Uniqueness Value

Hi Dieter,

Thanks guru! We're now satisified.

So the statement:

The system increments the uniqueness value by one for each successive table row having the same row hash.

Once the unique value mechanism is triggered its just concerned about checking whether it has the same row hash

The unique value mechanism doesn't know anything about the table id information since by the time it was called, the row insertion has already specified the data block therefore it's already implied that this just belongs to a single table

Thanks!

Pat

Re: Uniqueness Value

Hi Dieter,

i have 1 doubt . is it right , will clone table in teradata share same data blocks for new insert rows? 

Senior Apprentice

Re: Uniqueness Value

Of course not, because the new table will get a different tableid and within a datablock there's only data from a single table.

Dieter