FAQ
Hi,

A quick pl/sql question for oracle 9i. I have these two pl/sql blocks below,
does oracle treat them same internally when inside the loop?

Would "method 2" be better, in terms of performance?

Thanks.

Guang


method 1:
DECLARE

CURSOR c1 IS
SELECT ID, s1
FROM t1
WHERE s1 = 'abc';
FOR UPDATE;

BEGIN

FOR x IN c1 LOOP
UPDATE t1
SET StateID = 'NEW',
LASTMODIFIEDDT = sysdate
WHERE s1 = x.s1;
END LOOP;

END;

method 2:
DECLARE

CURSOR c1 IS
SELECT ID, s1
FROM t1
WHERE s1 = 'abc';
FOR UPDATE;

BEGIN

FOR x IN c1 LOOP
UPDATE t1
SET StateID = 'NEW',
LASTMODIFIEDDT = sysdate
where current of c1;
END LOOP;

END;

PRIVILEGED AND CONFIDENTIAL:

This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited. If you
are not the intended recipient, please notify the sender immediately by
return e-mail, delete this communication and destroy all copies.

Search Discussions

  • Richard Ji at Mar 17, 2005 at 11:39 pm
    This where you should just do it in SQL with a single
    update statement, not PL/SQL.
    On Thu, 17 Mar 2005 23:29:09 -0500, Guang Mei wrote:
    Hi,

    A quick pl/sql question for oracle 9i. I have these two pl/sql blocks below,
    does oracle treat them same internally when inside the loop?

    Would "method 2" be better, in terms of performance?

    Thanks.
    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Dick at Mar 18, 2005 at 9:01 am
    The first update would depend on an index on the s1 column to be
    efficient, otherwise it would do a full table scan looking for
    applicable rows. The second using the "current of" clause is equivalent
    to rowid =3D x1.rowid. Now if there are two or more rows where s1 has =
    the
    same value you've got two or more update statements that will get
    processed in method 2. Also, with the "current of" clause you can't
    commit after a number of rows as "for update of" causes a row level lock
    to be taken out on all of the rows returned. Doing a commit or rollback
    within the cursor will invalidate the cursor & cause the PL/SQL block to
    fail. Unless you've included an exit command.

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA

    -----Original Message-----
    From: Guang Mei =20
    Sent: Thursday, March 17, 2005 11:29 PM
    To: 'oracle-l_at_freelists.org'
    Subject: Question on update ... where current of ...=20

    Hi,

    A quick pl/sql question for oracle 9i. I have these two pl/sql blocks
    below,
    does oracle treat them same internally when inside the loop?=20

    Would "method 2" be better, in terms of performance?

    Thanks.

    Guang
    =20
    -- method 1:
    DECLARE

    CURSOR c1 IS

    SELECT ID, s1
    FROM t1
    WHERE s1 =3D 'abc';
    FOR UPDATE;
    BEGIN
    FOR x IN c1 LOOP
    UPDATE t1=20
    SET StateID =3D 'NEW',
    LASTMODIFIEDDT =3D sysdate=20
    WHERE s1 =3D x.s1;
    END LOOP;

    END;

    method 2:
    DECLARE

    CURSOR c1 IS
    SELECT ID, s1
    FROM t1
    WHERE s1 =3D 'abc';
    FOR UPDATE;

    BEGIN

    FOR x IN c1 LOOP
    UPDATE t1=20
    SET StateID =3D 'NEW',
    LASTMODIFIEDDT =3D sysdate=20
    where current of c1;
    END LOOP;

    END;

    *=20
    PRIVILEGED AND CONFIDENTIAL:=20

    This communication, including attachments, is for the exclusive use of
    addressee and may contain proprietary, confidential and/or privileged
    information. If you are not the intended recipient, any use, copying,
    disclosure, dissemination or distribution is strictly prohibited. If
    you
    are not the intended recipient, please notify the sender immediately by
    return e-mail, delete this communication and destroy all copies. =20

    *
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Guang Mei at Mar 18, 2005 at 9:15 am
    Dick,

    Thanks. That's the info I was looking for.

    Guang

    ....
    The second using the "current of" clause is equivalent
    to rowid = x1.rowid.
    .....

    -----Original Message-----
    From: Guang Mei
    Sent: Thursday, March 17, 2005 11:29 PM
    To: 'oracle-l_at_freelists.org'
    Subject: Question on update ... where current of ...

    Hi,

    A quick pl/sql question for oracle 9i. I have these two pl/sql blocks
    below,
    does oracle treat them same internally when inside the loop?

    Would "method 2" be better, in terms of performance?

    Thanks.

    Guang


    method 1:
    DECLARE

    CURSOR c1 IS
    SELECT ID, s1
    FROM t1
    WHERE s1 = 'abc';
    FOR UPDATE;

    BEGIN

    FOR x IN c1 LOOP
    UPDATE t1
    SET StateID = 'NEW',
    LASTMODIFIEDDT = sysdate
    WHERE s1 = x.s1;
    END LOOP;

    END;

    method 2:
    DECLARE

    CURSOR c1 IS
    SELECT ID, s1
    FROM t1
    WHERE s1 = 'abc';
    FOR UPDATE;

    BEGIN

    FOR x IN c1 LOOP
    UPDATE t1
    SET StateID = 'NEW',
    LASTMODIFIEDDT = sysdate
    where current of c1;
    END LOOP;

    END;

    *
    PRIVILEGED AND CONFIDENTIAL:

    This communication, including attachments, is for the exclusive use of
    addressee and may contain proprietary, confidential and/or privileged
    information. If you are not the intended recipient, any use, copying,
    disclosure, dissemination or distribution is strictly prohibited. If
    you
    are not the intended recipient, please notify the sender immediately by
    return e-mail, delete this communication and destroy all copies.

    *
    --
    http://www.freelists.org/webpage/oracle-l

    PRIVILEGED AND CONFIDENTIAL:

    This communication, including attachments, is for the exclusive use of
    addressee and may contain proprietary, confidential and/or privileged
    information. If you are not the intended recipient, any use, copying,
    disclosure, dissemination or distribution is strictly prohibited. If you
    are not the intended recipient, please notify the sender immediately by
    return e-mail, delete this communication and destroy all copies.

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 17, '05 at 11:32p
activeMar 18, '05 at 9:15a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase