Re: Newbie Oracle ROWID questions

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: Sat, 12 May 2001 04:06:18 +0800
Message-ID: <9dp12u$1h0$2@news.seed.net.tw>

"Jim Kennedy" <kennedy-family_at_home.com> ¼¶¼g©ó¶l¥ó
news:EnRK6.59$la.3439_at_news1.sttls1.wa.home.com...

> An update won't change the rowid. To change the rowid of a row you would
> have to delete the row and insert it. A row that gets larger and needs to be
> chained to another block will still have the original rowid.
>
> I have used rowids for this very purpose for years and it works very well.
> One caveat is to not store the rowid somewhere and use it days later. If
> someone did an export, drop the table, and a reimport the rowids would then
> be different.
>
> That said yes, it is a much better design to have a primary key.
> Absolutely.
> Jim

Well, there are some exceptions.
An UPDATE would change the ROWID in the following cases (as I know)

To update the primary key of a Index-organized table on Oracle8i. This is not true on Oracle8.0.x, since IOTs do not have a ROWID.
On Oracle8i, on a partitioned table with ENABLE ROW MOVEMENT option, if an update causes the rows moved from a partition to another partition, the ROWIDs must be changed, since the physical location is changed.

There may be some other cases I don't know.

It's better not design based on ROWIDs.
For example, you query the ROWID,
and some one delete and insert the row,
or some one delte a row and some one else insert a row just before you update, the ROWID may be changed, then your program would occurs an unexpected error. Received on Fri May 11 2001 - 15:06:18 CDT