RE: Question on update ... where current of ...

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 [mailto:GMei_at_ph.com]=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;

• 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