FAQ
when you update inside of a cursor you have two steps.

Fetch data into the cursor
Update the table based on what you selected.

So while you are selecting data and before you update, the record could change. This means someone else could update it and youc ould update over it without knowing it. This is a bad idea in the database world and is known as 'disapearing updates'. So you lock the row when you select it. Not when the actual update is processed.

Updating inside of cursors is generally a bad idea. Its more code and its MUCH slower than a straight update statement. Ive seen some publications stating that it can before to do this way, but Ive tested every case I can think of and not once was it even close.

what is the update statement you want to put inside the cursor?
From: MaryAnn Atkinson
Date: 2003/07/11 Fri PM 04:09:25 EDT
To: Multiple recipients of list ORACLE-L
Subject: RE: full usefullness of CURRENT OF ???

--- 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.com
San 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: http://www.fatcity.com
San 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).

Search Discussions

  • Jared Still at Jul 12, 2003 at 1:24 am

    On Friday 11 July 2003 13:19, rgaffuri_at_cox.net wrote:
    So while you are selecting data and before you update, the record could
    change. This means someone else could update it and youc ould update over
    it without knowing it. This is a bad idea in the database world and is
    known as 'disapearing updates'. So you lock the row when you select it. Not
    when the actual update is processed.
    No, you don't lock a row when you select it.

    There's no need for a read lock of a row in Oracle to maintain consistency.
    If Oracle can't produce a consistent row, it throws an ORA-1555.

    Jared
    Updating inside of cursors is generally a bad idea. Its more code and its
    MUCH slower than a straight update statement. Ive seen some publications
    stating that it can before to do this way, but Ive tested every case I can
    think of and not once was it even close.


    what is the update statement you want to put inside the cursor?
    From: MaryAnn Atkinson
    Date: 2003/07/11 Fri PM 04:09:25 EDT
    To: Multiple recipients of list ORACLE-L
    Subject: RE: full usefullness of CURRENT OF ???

    --- 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.com
    San 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: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San 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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 11, '03 at 8:19p
activeJul 12, '03 at 1:24a
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase