Re: Newbie Oracle ROWID questions

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

"wayne" <no_at_email.please.com> wrote in message news:9deldt$hu_at_dispatch.concentric.net...
> > BUTT - there must be a catch somewhere. What are the pitfalls of using
> > ROWID as a direct access capability to do UPDATES?.
>
> The biggest catch is that you should not depend on the ROWID to remain
> constant. Since a components of the ROWID is the physical location of the
> row, and since the ROWID may vary for many reasons (even a simple update
 may
> change the ROWID of the record), you have to be very careful with it.
>
> RowIDs are _very_ temporary, so handle them with great care, and never
> assume the ROWID will remain the same for any row for too great a time.
>
> It would be much better design if the critical tables had a primary key
> (which by definition has to be unique). If the data does not lend itself
 to
> having a unique primary key, create one based on a sequence (populate it
> with an ON INSERT trigger). You can then be assured that 1) you can
 safely
> use the primary key the same way as you would the ROWID, and 2) the
 primary
> key will not change, while the ROWID can change many times for a number of
> reasons.
>
> With ROWID you are asking for trouble. Go the primary key way if you want
> to save yourself from big headaeches.
>
>
Received on Fri May 11 2001 - 08:00:20 CDT