Re: When does ROWID change ?

A copy of this was sent to "Alan Shein" <alanshein_at_erols.com> (if that email address didn't require changing) On Tue, 2 Nov 1999 09:44:42 -0500, you wrote:

>
>Jenda Krynicky <Jenda_at_Krynicky.cz> wrote in message
>news:1103_941539499_at_prague_main...
>> Is it safe to use a ROWID as a key for some table?
>
>NO NO NO!!!!!!!!!!! ROW IDs are dynamic, and you have no control over them.

rowids are static with the exception of 2 cases in Oracle8i -- release 8.1 (in 8.0 and before they are 100% static, assigned when you insert and never changing)

o if you update a partition key and that update causes the row to migrate to a new partition it is processed much like a delete/insert.

o if you issue "alter table T move ..." and physically move the table (recreate the table elsewhere) the rowids will change. You must rebuild all indexes after doing this.

If the rowids changed willy nilly -- indexes would not work very well :)

I agree with your conclusion though -- don't use the rowid as a surrogate key. rowids are great when used within a single transaction (eg: to help you do row locking ALA forms for example) but storing them in a table as a pointer is probably a bad idea long term.

>There is no guarantee that any particular row will ever have the same row
>id. Remember that relational theory says that sets (data, in this case) are
>not stored in any particular order. Your data is in a bucket, not on
>shelves. It just so happens that most of the time, you have a very steady
>hand and can retrive the data from the bucket the same way you did last
>time, but there's no guarantee...
>
>You may need to create an artificial key (such as a sequence number).
>
>
>

--
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 - 13:40:26 CST