Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!turnkey!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <24358@dice.la.locus.com>
Date: 14 May 91 19:54:46 GMT
References: <22653@shlump.lkg.dec.com>
Organization: Locus Computing Corp, Los Angeles
Lines: 75

In article <22653@shlump.lkg.dec.com> saunders@refine.enet.dec.com (Kyle Saunders) writes:
>
>In article <24334@oolong.la.locus.com>, jfr@locus.com (Jon Rosen) writes...
>|In article <12260@mentor.cc.purdue.edu> tmp@mentor.cc.purdue.edu (Tom Putnam) writes:
>|>In article <22626@shlump.lkg.dec.com> saunders@refine.enet.dec.com 
>|>(Kyle Saunders) writes:
>|>>	I was wondering how you would implement cursor based UPDATE's and 
>|>>DELETEs. I have given some thought to this and cannot see how the RDBMS can
>|>>properly update the correct row given the fact that the table to be updated
>|>>may not have _any_ indexes, and not all fields are guarenteed to be included
>|>>in the query.  This means there is no way to uniquely determine which row in
>|>>the original base table is being updated.
>|><lotsa stuff deleted...>
>
>	Keeping an explicit pointer for each row generated by an OPEN cursor
>to the original base table is a _major_ kludge IMHO.  That's special casing
>this action.  What's worse is letting users access this ROWID.  If ROWID is
>provided in any SELECT, why?!?  Users will abuse this bit of information.  The
>user should not see the underlying physical implementation of the Relational
>Model, if at all possible.
 
  Why do you feel this is a kludge?  You have to do SOMETHING!  In another
  post you mention creating some kind of index on the fly when a DECLARE
  CURSOR is used with a FOR UPDATE clause .... Well, what else is an
  index but a key plus a rowid (albeit a strictly internal one)  which lets
  you get back to the original row?  If you don't have the primary key (i.e.,
  whether that is a subset of the columns or the complete set of columns
  is irrelevant as long as you have a primary key and no duplicate rows)
  available in your query, you either have to grab a unique row reference
  and include it with each row (call it what you will, a row id, a pointer,
  a link, I don't care it still gets you back to the row) or you have to
  supplement the query to include the entire primary key.  If the primary
  key has lots of columns (sometimes the whole row) this can certainly
  degrade performance more than keeping a row id.  And I don't think it
  is any less a kludge than the row id.  

>
>|Also, it is not as bad as Tom thinks to keep using this ROWID.  Usually,
>|the ROWID is a logical construction and has nothing (or little) to do
>|with the physical location/inode/record number of the actual row.  So 
>
>	How is it a logical construction?  How is it safe to be anything _but_
>the actual record number (guarenteed to be unique without having to look-up or
>generate anything)?
 
  Hmmm, I have to go back and look at my original post.  What I was trying
  to convey is that a ROWID in Oracle is a logical construct, not necessarily
  a physical one.  I believe it is a uniquely generated sequential number
  that is guaranteed not to change even if the row is moved.  If this is
  not the case (my Oracle recollections may be fuzzy) it certainly SHOULD
  be.  My TIMESTAMP rowid would be similar.  This is as opposed to using
  something like the sector-offset value for the location of the row on
  disk as the rowid.  Obviously, this is fraught with peril since the row
  can move somewhere else and its internal id can then change.  But I agree
  I may have been unclear on this point.

>|As for the duplicate row issue, don't you know that relational data
>|bases aren't supposed to have duplicate rows??? :-)
>
>	I never mentioned duplicate rows.  
>

  Oh... I thought you did... I looked back and you are right, you didnot.
  If that is the case, you have an implicit primary key in all tables
  and you are home free either with a rowid or by including all values
  of the primary key for each table in the query.  Any other approach
  is NOT supported by the relational model.  That is actually the main
  point.  The relational model expressly supports updating ONLY through
  the use of the primary key which means you have to have one and it
  has to be available at the time of the update.  The only problem 
  with that solution is that sometimes the performance using the
  primary key sucks.  Thus, the use of rowids (kludgy or otherwise).
 
  Jon Rosen
  No flames intended :-)