Re: Row ID

On Sat, 4 Sep 1999 12:41:26 -0400, you wrote:

>ROWID identifies the place where Oracle has stored the row. Anything that
>causes rows
>to be relocated makes an old ROWID Obsolete.
>

No, there is only 1 case where a relocated row will change a rowid. It is only in Oracle8i release 8.1. It is when you update a partition key and have explicitly enabled row movement. When you enable row movement in a partitioned table, an update to the partition key is processed much like a DELETE/INSERT rather then an update.

If a row migrates (eg: it was too big to fit in the block it was on after an update) -- the rowid does not change (consider the havoc this would play with indexes which rely on the rowid not changing for the life of a row).

Unless you enable row movement AND have Oracle8i AND update the partition key -- a rowid will never change for the life of a row. You have to delete the row and insert it again to get a new rowid assigned to it (but its not the same row anymore -- its a new row)

That aside -- using a rowid for an audit table would be a very bad idea as a unload/reload of the data would lose the connection between the data and the audit trail (i didn't suggest they use a rowid btw -- they should not in this case). I just want to point out that the rowid is a good thing to use in many cases. Forms for example puts it to excellent use. Forms will silently select out the rowid for every row it can (views and such might not permit a rowid). It will use this rowid to help perform row locking as fast as possible. For example, when you build a block on the EMP table using just the ENAME and JOB columns, forms "selects rowid, ename, job from emp". When you attempt to update a row in that block, forms issues:

select * from emp where rowid = :block.rowid and ename = :block.ename and job = :block.job FOR UPDATE NOWAIT; If that returns exactly 1 row (and the access path is by rowid -- very fast) then you have locked your row and the data hasn't changed.

if that returns zero rows -- someone else has changed the data -- you cannot update it.

if that returns an error -- someone else has that row locked, you'll have to wait.

That shows that using a rowid in a session works very well. Its a good, small, surrogate primary key that is very safe to use in a smallish time window. Don't save it in a table but by all means use it in your apps. Web based forms can especially make use of the rowid from screen to screen to remember what row(s) they were working on.

>If you have the "Oracle Concepts" manual (two volumes in the doc set),
>look up
>ROWID in there. You'll see a good treatment of when to avoid using ROWIDs,
>which is basically almost all the time.
>
>If you want to pin your records, go back to IMS or IDMS. Relational isn't
>for you.
>
>Fraser Boswell wrote in message <37CCF3C7.2945103D_at_spamme.ed.ac.uk>...
>>I'm recording the RowID of records in an Audit table (thanks Thomas Kyte
>>for the advice), but was wondering what would destroy the RowID making
>>it invalid. If all records in the table are copied to a new table, old
>>table dropped and new table renamed back to the original, would the
>>RowID be invalid for the new table?
>>What else would cause the RowID to become out of step with the table?
>>
>>Thanks in advance
>>
>>Fraser
>>
>
>
>

--
See http://govt.us.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 Sat Sep 04 1999 - 12:37:32 CDT