Locking if something oracle is very good at. It happens all over the place
and it happens quickly...there is very little overhead to it.
Block headers. ...each block in the affected table.
By updating the table by rowid, you get the best of both worlds assuming no
one is re-orging the table (or somehow altering rowid to row value
relationship) and that there is no need prevent users from touching the
table while you are doing the update.
Cursor c_crud is select rowid, col1 from t_crud;
For v_crud in c_crud loop
Update t_crud
Set col1=whatever
where rowid = v_crud.rowid;
End loop;
(I remember reading about this in Guy Harrison's SQL tuning book...if you
have a copy, I'd check that out....because I am very prone to memory recall
error :) )
-----Original Message-----
Sent: Friday, July 11, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
Chris Stephens wrote:
Also, the CURRENT OF locks the table so that no one can modify (or
even read) it while the transaction is taking place. This guarantees
nothing is changing between retrieving values from the cursor and
updating the table based on those values.
OK, fair enough.
The second example has to run the update statement seperately.
CURRENT OF can go directly to the row(s) affected. CURRENT OF
still has to modify each block header in the table to lock which
is a small performance hit.
So CURRENT OF has to lock, so its slower, right?
Does it have any advantages after all?
To prevent that you could update by rowid
How? How can I update by ROWID? I was thinking to update
by PRIMARY_KEY...
and avoid the header updates.
... what "header" updates?
thx
maa
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net--
Author: MaryAnn Atkinson
INET: maryann_30_at_yahoo.com
Fat City Network Services -- 858-538-5051
http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net--
Author: Chris Stephens
INET: ChrisStephens_at_affina.com
Fat City Network Services -- 858-538-5051
http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).