How to use ROWIDs?

[maheshm]

I am new to Tearadata and while playing with Queryman I noticed Teradata returns ROWIDs in a SELECT statement.

Example:

SELECT ROWID, UserId, AccountName from Accounts;

ROWID UserId AccountName
1317B97900000100 00000100 DBC
56123D3C00000100 0000F203 DBC
.
.
.

Anybody knows more about ROWIDs and how to use them in a query? Oracle lets us make queries on ROWIDs in WHERE clause. I wonder if it works similarly in Teradata.

BTW, anyone knows how I can use BYTE fields in queries?

Example:
SELECT * from Accounts WHERE UserId=00000100;

When I do this I get:
3640: Camparing BYTE data in column UserId with other types is illegal.

Anyone knows if I am allowed to do such a query or if there is a special syntax for it.

thanks in advance.

 

[wes23]

Why would you want to use rowid? There is no business value. I think the teradata manuals advise against using rowid because it's use may change at anytime.

 

[tdatgod]

Hi,
Here is a reply I just posted to another teradata forum

http://www.teradataforum.com

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.