Archives of the TeradataForum

Message Posted: Tue, 19 Feb 2002 @ 09:48:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Now As for selecting just a single row instead of muliple rows, C.J. Date points out that a PRIMARY KEY is just one of possibly many CANDIDATE KEYS and any CANDIDATE KEY regardless of whether it is chosen as the Primary Key can be used to UNIQUELY identify the row.

Now what the relationship between your PRIMARY INDEX and your PRIMARY KEY is is part of the physical database design. Incidently, Foreign Keys are also required to be Candidate keys in another table.

ROWID is an internal concept to teradata and should be treated as Such. Rowid of a row can change from table to table even for the same data.

As such, since the Teradata Rowid is not guarenteed to always select the same row I would advise against using it as a Candidate Key.

You should use CSUM or RANK or one of the other techniques for numbering the rows yourself rather than relying on teradata Internal numbering to do it for you.

The reason is Row ID is made up of 2 parts

ROW HASH and ROW UNIQ

The HASH is the value given by taking the key and running it throught the teradata hash function. This will be identical for all identical data (called a collision). In Addition, it may be identical for disparing data if the just happen to HASH to the same value (called a synonym )

The UNIQ portion of the row id is just the highest number of HASH collisions or synonyms that ROW HASH has had within that table.

If you insert 2 rows and they just happen to end up at the same row HASH which one has a UNIQ of 0 1 and which has the UNIQ of 0 2 is not guarenteed.

In fact if you then delete the row which has the UNIQ 0 2 and insert another row which ends up at the same hash it could very well be inserted as UNIQ 0 2 or it might be inserted as 0 5.

Again there is no Guarentee on what the UNIQNESS value of an inserted row will be until it is actually inserted.

If you INSERT SELECT the rows, either in part or whole into another table, the UNIQNESS value of the row could change.

	<Prev	Next>		<<First	<Prev	Next>	Last>>