Re: Newbie Oracle ROWID questions

From: Peter Kalapuziak <peteropd_at_getnet.com>
Date: Sat, 12 May 2001 15:29:57 -0700
Message-ID: <OSiL6.740$TI6.460816@news.uswest.net>

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message
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.

Yes, this is also what I am meaning to do. Get the table data with the ROWID, make the mods and issue the update immediately using the ROWID. I never intended to hold the ROWID 'hostage' until some later time... As long as the ROWID remains 'intact' during the nano-second lifecycle of the Fetch/Update, then that's all I'm looking for. Looks like I'll give it a whirl!

>
> That said yes, it is a much better design to have a primary key.
> Absolutely.

Agreed - if we get a second go at designing (re-designing) the database, now that the user's have finally understood what they want, this will be the case.

> 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 Sat May 12 2001 - 17:29:57 CDT