Re: Informix vs. Oracle

What is the impact of what you suggest on performance vs. having one cursor active? It would seem quite costly. What if there are tens of thousands of rows in the cursor? -Bill } } 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 } -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net