| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: When does ROWID change ?
A copy of this was sent to Jenda_at_Krynicky.cz (Jenda Krynicky)
(if that email address didn't require changing)
On Tue, 02 Nov 1999 10:44:59 GMT, you wrote:
>Is it safe to use a ROWID as a key for some table?
>
>I have a table that doesn't have any reasonable key
>(I'd have to use the "sum" of many keys as the key).
>Into another table I compute some data which should be tied to the rows in the first table.
>Many rows from Table2 to one in Table1, most rows in Table1 do not have anything in Table2.
>
>I thought it's safe to tie the tables using Table1.ROWID as the key. I thought the ROWID would change only if
>I deleted and recreated the rows in Table1, or if I copied the data over to a different server.
>Is this assumption correct? Does "Analyze table" change the ROWIDs?
>
the only 2 things I can think of that change a rowid are: (note: if you delete and reinsert a row -- you have not changed its rowid, you've created a brand new row IMO)
o an update to a partitioned key that causes the row to migrate from one partition to another (and that is only possible in Oracle8i, release 8.1).
o using the alter table T move .... (a move of a table -- great way to get table T from tablespace1 to tablespace2 without export import). Again, this is only possible in Oracle8i, release 8.1
A rowid is assigned when a row is inserted and will not change (with the exception of the above cases). I should point out however that the rule of "a rowid never changes" is starting to be broken. You could reasonably expect over time more and more of these cases to occur. It is my opinion that a rowid should only be used during the course of a transaction (forms does this very well for example) -- it should not be used as a surrogate key and stored persistently.
The reasons are mounting, among them are the fact that some tables might not even have rowids -- consider:
tkyte_at_8.0> create table t ( x int primary key , y int ) organization index; Table created.
tkyte_at_8.0> insert into t values ( 1, 2 ); 1 row created.
tkyte_at_8.0> select rowid from t;
select rowid from t
*
but in Oracle8i, release 8.1 they have a rowid again (a UNIVERSAL rowid -- a new type of rowid)
tkyte_at_8i> create table t ( x int primary key , y int ) organization index; Table created.
tkyte_at_8i> insert into t values ( 1, 2 ); 1 row created.
tkyte_at_8i> select rowid from t;
ROWID
It would be much better if you altered the table, added a numeric column, made it unique, created a trigger to populate this column via a trigger and used this surrogate key.
>It seemed to work OK at first but I get some strange results now and want to make sure this is not the problem..
>
>Thanks, Jenda (CC: would be appreciated)
>http://Jenda.Krynicky.cz
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 02 1999 - 06:14:16 CST
 |
 |