Re: changing ROWID

Since it appears that the weight of opinion has begun to be apparent for this thread,
I'd like to try to summarize things to see if I understand the situation. This is a long
document, so you should only spend the time reading it if you want to help me out
by telling me where I got things wrong, or if you are looking for a summary of the
things said in this thread.

The original question posted concerned whether or not ROWIDs could be considered
reliable, and what to do in the event that a SELECT using a ROWID failed. In particular,
I wanted to know if a simple UPDATE could affect a ROWID.

The simple answer appears to be "No." ... with a number of cautions. Several individuals who work at (or with) Oracle Corporation stated that Oracle has
made great efforts to insure that only operations which create or destroy a row will
impact the ROWID. This means that a simple update should *never* change a ROWID,
and that if anyone has experienced such a ROWID change, they should report it immediately
to the Oracle Corporation as a serious problem. Apparently, as of this time, there are
no reports of such an error.

Does this mean, then, that a ROWID is constant during the "lifetime of a row" (as stated
in the Oracle documentation)? If you defined the "lifetime" of a row to be the time from
when an INSERT command was issued for the row, until the time a DELETE command is
issued, then the answer appears, again, to be a "No.". Several behind-the-scenes operations
could lead to a situation where the row is physically removed from the database and re-created,
resulting in a change of the ROWID. The most commonly cited operation was a database
import/export, though some people indicated that certain (unnamed) database management
tools might force a change in the ROWID. I suspect there are a number of other tools and
operations which might cause the row to be destroyed and re-created, but I'm not a DBA,
so I can't name any.

Some people expressed the opinion that, if an UPDATE command caused the row to become
physically larger, this might force the row to be relocated on the disk. Since ROWIDs are
based upon the physical location of the row on the disk, this would appear to mean that
the ROWID will change. This is not the case. The Oracle DBMS software will place a
pointer at the old position indicating the new position, a therefore the old ROWID will still
work. This operation is called "migrating" in Oracle 7. One poster also mentioned something
called "chaining", in which "rows (are) smeared across blocks", but it wasn't clear to me
what this was.

There were also some interesting side comments important to consider. In no particular
order, they are; a) if a row is deleted, a new row in the same table could be created with
exactly the same ROWID; b) two rows from different tables can share the same ROWID;
c) placing a lock on the row with a FOR UPDATE clause should prevent any changes
to the ROWID during a transaction.

So, for those of you, like me, who are writing a program that needs to save a ROWID, you
would be best served if you use the FOR UPDATE clause in the SELECT statement
you use to read the ROWID in. This should guarantee that the ROWID is unchanged until
the next commit or rollback. In this case, if a SELECT using the ROWID fails, and you
are certain that the ROWID has not been corrupted by your program, call Oracle. You've
found a serious problem.

If, for some reason, you cannot lock the row during your read of the ROWID, if the
subsequent SELECT using the ROWID fails, it could be caused by all manner of different
things - the record was deleted, the database was reorganized, etc. The most likely
scenario is that the row was deleted, but this is not guaranteed. Even if the SELECT is
successful, you may wish to check the data returned to make sure you read the record
you really wanted, and not some new one that took it's place. So, an unprotected
read using a ROWID can be unpredicatable.

This is what I gleaned from this thread. If I have been mistaken about any of this points,
please accept my apology, and mail me a note telling me where I went wrong.

Thanks for all of your advice. Received on Mon Jan 16 1995 - 19:08:14 CET