Nonsense (was Re: Changing ROWID)

>From: mfriedma_at_oracle.uucp (Mike Friedman)
>Steve Edelstein <74160.645_at_CompuServe.COM> writes:
 

>>An update only changes the rowid id the row is moved.
 

>To the best of my knowlege, and update can not move a row.
>Therefore, an update never changes a rowid.

Nonsense. You are failing to distinguish between what a DBA considers an update (an SQL update statement) and what an application might consider an update (which might very well be a deletion and an insertion).

Consider an "update" that changes the subtype of a "row", but you've implemented two subtypes as actually two physical tables (which you have hidden from the applications and users). Such an update certainly does change the ROWID value.

>>If the
>>update happens normally, and the updated data is too big to fit
>>into the original block of the parent row (say adding a large
>>chunk of data to the row), then Oracle will chain to another
>>block to store the new data. The rowid shouldn't change at that
>>point since the parent of the new data is in the same place.

I believe this is correct for SQL update statements.

The CRUCIAL thing to remember about ROWIDs is this: they are the PHYSICAL location of the (beginning of the) row on disk. *ANYTHING* that might change this, such as a tablespace re-org, will almost assuredly change the ROWID. An export/import (which defrags the tablespace) will almost certainly change the ROWID. A crash and restore from backup may very well change ROWIDs, depending on the backup and restore method. An unload/load with SQL*Plus and SQL*Loader will almost certainly change the ROWID.

Yet in each of these cases, it's still the "same" row, isn't it?

The format of a ROWID is this:

                DataBlockID:RowNumInBlock:DataFileID

Clearly a ROWID is nothing other than a physical address, and like any physical aspect of the data storage, should not be used by applications that address the logical level.

Refer to the Oracle7 Server SQL Language Reference Manual, page 2-42, "ROWID".

The statement "a ROWID does not change durint the lifetime of its row" is clearly a statement about a physical row on disk. Any other interpretation is nonsensical. And anyone who relies on the (physical) ROWID to try to reliably work with (logical) data is asking for trouble, IMHO.

Cheers.

 -Tom Received on Fri Jan 13 1995 - 21:09:50 CET