FAQ
Could anyone give us an idea as to the full usefullness
of CURRENT OF? Here are my two separate examples:

/* 1 this one has a CURRENT OF */

DECLARE

CURSOR EmpCursor IS

SELECT *
FROM Emp
FOR UPDATE;

BEGIN
FOR EmpRec IN EmpCursor LOOP

UPDATE EMP
SET SALARY = SALARY * 1.08
WHERE CURRENT OF EmpCursor

END LOOP;

END;

/* 2 same as above, except without the CURRENT OF */
DECLARE

CURSOR EmpCursor IS

SELECT *
FROM Emp
FOR UPDATE;

BEGIN
FOR EmpRec IN EmpCursor LOOP

UPDATE EMP
SET SALARY = SALARY * 1.08
WHERE EMP_ID = EmpRec.Emp_ID;

END LOOP;

END;

/

Is one more efficient than the other? If I could have done
business without the CURRENT OF, then why did oracle made
it available?

thx
maa

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

Search Discussions

  • Chris Stephens at Jul 11, 2003 at 7:14 pm
    I believe (I could be totally wrong here) the reason for the CURRENT OF is
    both for performance and consistency.



    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. To
    prevent that you could update by rowid and avoid the header updates.



    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.



    -----Original Message-----
    Sent: Friday, July 11, 2003 1:54 PM
    To: Multiple recipients of list ORACLE-L



    Could anyone give us an idea as to the full usefullness

    of CURRENT OF? Here are my two separate examples:





    /* 1 this one has a CURRENT OF */



    DECLARE



    CURSOR EmpCursor IS

    SELECT *

    FROM Emp

    FOR UPDATE;



    BEGIN

    FOR EmpRec IN EmpCursor LOOP



    UPDATE EMP

    SET SALARY = SALARY * 1.08

    WHERE CURRENT OF EmpCursor



    END LOOP;



    END;





    /* 2 same as above, except without the CURRENT OF */

    DECLARE



    CURSOR EmpCursor IS

    SELECT *

    FROM Emp

    FOR UPDATE;



    BEGIN

    FOR EmpRec IN EmpCursor LOOP



    UPDATE EMP

    SET SALARY = SALARY * 1.08

    WHERE EMP_ID = EmpRec.Emp_ID;



    END LOOP;



    END;

    /





    Is one more efficient than the other? If I could have done

    business without the CURRENT OF, then why did oracle made

    it available?



    thx

    maa



    --

    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: Chris Stephens
    INET: ChrisStephens_at_affina.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).
  • Kevin Toepke at Jul 11, 2003 at 7:24 pm
    Yes, using WHERE CURRENT OF is faster. It is the same as saying "WHERE rowid
    = emprec.rowid", just more readable. And accesses by rowid are faster than
    index accesses.

    Also, what happens if you don't have a unique or primary constraint on the
    table and whole rows can be duplicated? Then you pretty much need to use
    WHERE CURRENT OF.

    HTH

    Kevin

    -----Original Message-----
    Sent: Friday, July 11, 2003 2:54 PM
    To: Multiple recipients of list ORACLE-L

    Could anyone give us an idea as to the full usefullness
    of CURRENT OF? Here are my two separate examples:

    /* 1 this one has a CURRENT OF */

    DECLARE

    CURSOR EmpCursor IS

    SELECT *
    FROM Emp
    FOR UPDATE;

    BEGIN
    FOR EmpRec IN EmpCursor LOOP

    UPDATE EMP
    SET SALARY = SALARY * 1.08
    WHERE CURRENT OF EmpCursor

    END LOOP;

    END;

    /* 2 same as above, except without the CURRENT OF */
    DECLARE

    CURSOR EmpCursor IS

    SELECT *
    FROM Emp
    FOR UPDATE;

    BEGIN
    FOR EmpRec IN EmpCursor LOOP

    UPDATE EMP
    SET SALARY = SALARY * 1.08
    WHERE EMP_ID = EmpRec.Emp_ID;

    END LOOP;

    END;

    /

    Is one more efficient than the other? If I could have done
    business without the CURRENT OF, then why did oracle made
    it available?

    thx
    maa

    --
    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).
  • MaryAnn Atkinson at Jul 11, 2003 at 8:09 pm

    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).
  • MaryAnn Atkinson at Jul 11, 2003 at 8:14 pm

    Kevin Toepke wrote:
    Yes, using WHERE CURRENT OF is faster. It is the same as saying
    "WHERE rowid = emprec.rowid", just more readable. And accesses by
    rowid are faster than index accesses.

    Also, what happens if you don't have a unique or primary constraint
    on the table and whole rows can be duplicated? Then you pretty much
    need to use WHERE CURRENT OF.
    HTH
    Kevin
    I see, its beginning to make sense...

    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).
  • Daniel Fink at Jul 11, 2003 at 8:19 pm
    Repeat after me...readers never block writers...writers never block readers...

    The update will lock the row(s) of the result set, but it will not block anyone from reading the data. However, they may not read the modifications made by the update until it is committed.

    MaryAnn Atkinson wrote:
    --- 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: Daniel Fink
    INET: daniel.fink_at_sun.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).

    text/x-vcard attachment: Card for Daniel Fink
  • Kevin Toepke at Jul 11, 2003 at 8:19 pm

    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 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).
  • Chris Stephens at Jul 11, 2003 at 8:34 pm
    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.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: Chris Stephens
    INET: ChrisStephens_at_affina.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).
  • Maryann Atkinson at Jul 11, 2003 at 9:04 pm
    Could anyone give us an idea as to the full usefullness
    of CURRENT OF? Here are my two separate examples:

    /* 1 this one has a CURRENT OF */

    DECLARE

    CURSOR EmpCursor IS

    SELECT *
    FROM Emp
    FOR UPDATE;

    BEGIN
    FOR EmpRec IN EmpCursor LOOP

    UPDATE EMP
    SET SALARY = SALARY * 1.08
    WHERE CURRENT OF EmpCursor

    END LOOP;

    END;

    /* 2 same as above, except without the CURRENT OF */
    DECLARE

    CURSOR EmpCursor IS

    SELECT *
    FROM Emp
    FOR UPDATE;

    BEGIN
    FOR EmpRec IN EmpCursor LOOP

    UPDATE EMP
    SET SALARY = SALARY * 1.08
    WHERE EMP_ID = EmpRec.Emp_ID;

    END LOOP;

    END;

    /

    Is one more efficient than the other? If I could have done
    business without the CURRENT OF, then why did oracle made
    it available?

    thx
    maa

    --
    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).
  • Tanel Poder at Jul 11, 2003 at 9:14 pm
    Hi!
    Actually, it is the FOR UPDATE that locks the table.....
    No, for update still locks *only the rows* selected with for update clause.
    All other parts of table are available for both reading AND writing (of
    course if other transactions don't have locks on some rows).
    And you can read the whole table, despite any insert, delete, update or for
    update clauses.

    So, don't give false information here.

    There's an exception (as usual), if you have set ROW_LOCKING init parameter
    to INTENT, then the whole table is locked during update clauses. Also, you
    can use lock table command to lock tables if needed, but this goes beyond
    scope of this posting...

    Tanel.
    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 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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Tanel Poder
    INET: tanel.poder.003_at_mail.ee

    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 6:54p
activeJul 11, '03 at 9:14p
posts10
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase