Oracle-L: RE: full usefullness of CURRENT OF ???

From: Kevin Toepke <ktoepke_at_rlcarriers.com>
Date: Fri, 11 Jul 2003 12:19:37 -0800
Message-ID: <F001.005C4647.20030711121937@fatcity.com>

>> 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.

Actually, it is the FOR UPDATE that locks the table.....

>How? How can I update by ROWID? I was thinking to update
>by PRIMARY_KEY...

DECLARE CURSOR EmpCursor IS

           SELECT  rowid, ....
           FROM    Emp;
BEGIN
     FOR EmpRec IN EmpCursor LOOP
         UPDATE EMP
         SET    SALARY = SALARY * 1.08
         WHERE  rowid = EmpRec.rowid;
     END LOOP;

END; Kevin
-----Original Message-----
Sent: Friday, July 11, 2003 4:09 PM
To: Multiple recipients of list ORACLE-L

Chris Stephens <ChrisStephens_at_affina.com> 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: Kevin Toepke
  INET: ktoepke_at_rlcarriers.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).

Received on Fri Jul 11 2003 - 15:19:37 CDT