Re: Informix vs. Oracle

On Mon, 25 Aug 1997 12:05:55 GMT, Nils.Myklebust@idg.no (Nils Myklebust) wrote: >tkyte@us.oracle.com (Thomas Kyte) wrote: > >:Yes, regular cursors may be held open across commits. a SELECT ... FOR UPDATE >:cursor may not be. > >But a select ... for update cursor might be the important one to hold >open. I can understand why they don't when they first lock all rows. >That seems to me to be another negative effect of this behaviour. > >For our purposes, OLTP but a lot of updates of large number of rows, >the Informix solution seems to be the best by far overall. > Well, perhaps you are committing in Informix because it cannot handle infinitely many locks? I would not commit in an update transaction until I was *done* in the update transaction myself. It is trivial to support this (the above) however in Oracle as such: for x in ( select rowid from T where ... ) loop select * into SomeRec from T where rowid = x.rowid for update; ..... do whatever .... if ( someCondition ) then commit; end if; end loop; (note: rowid in Oracle is different from rowid in Informix. The rowid is the physical address (file/block/slot in block) of a row, it is the fastest way to get the data as it does one IO and since you are reading the table in the above example, it doesn't really matter -- the block is already cached). > >Nils.Myklebust@idg.no >NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway >My opinions are those of my company >The Informix FAQ is at http://www.iiug.org Thomas Kyte tkyte@us.oracle.com Oracle Government Bethesda MD http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation