RE: faster way of doing this?

From: Justin Mitchell <jmitchell_at_taxwise.com>
Date: Wed, 23 Mar 2005 15:58:24 -0500
Message-ID: <59426D03782DBD42AED3E85C3DAFED833C9893@exch01.utsad.com>

That commit you took out is pretty important, it's the whole point of doing the statement this way, and it's the whole point of Tom's answer (and the original question). =20

Tom says:
"The second loop keeps NO cursors open across commits. That is why is avoids the 1555. It is a cursor opened against a table that you are modifying in a loop and you commit frequently that causes the 1555." And=20
"It is when you comit across fetches that you do this to yourself."

What he means is that if you're going to do commits in your loop, you need to pull a new dataset afterwards to avoid your error. That's what the error is telling you too (since it's trying to get a consistent read).

Based on what I see, your old code works faster because it's pulling the dataset once, whereas the other code is pulling it 13 times, each time getting a different dataset to delete. But you're negating any performance benefits by not committing because you're still generating essentially the same amount of rollback.

This method will be slower than doing a single bulk commit or even a single loop just due to the fact that you are pulling the dataset more often, but if you need to do that for your rollback segments, then you may not have much of a choice.

If you do end up doing it this way, experiment a bit and see how high you can get your "rownum<=3DX" part. The less number of times you run the statement, the less time it will take.

Thanks,

Justin Mitchell

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei Sent: Wednesday, March 23, 2005 3:37 PM
To: Oracle-L (E-mail)
Subject: faster way of doing this?

Hi,

The code needs to work on oracle 8i and 9i. The current code occasionally
gave "Ora-01555: Snapshot too old" error (becuase there is a commit within
the loop for each 1000 rows, which I took out in my test code below). Assuming we can not change the RBS size, and I found Tom suggested to do the
bulk delete=20

http://asktom.oracle.com/pls/ask/f?p=3D4950:8:17490901823893610313::NO::F4 950_
P8_DISPLAYID,F4950_P8_CRITERIA:1046580359875 However my test showed the new method ran slower. Is there a better/faster
way of writing the same delete sql? The total number of "to-be-deleted" rows
could be huge in the table, so I need to use rownum<=3D1000. TIA.

Guang

set timing on
set serveroutput on

current method:

declare

counter number;
cursor DelAudit_cur1 is

	   SELECT AuditTrail.RID
       FROM   AuditTrail, Customers, Banks, auditedUsers
       WHERE  AuditTrail.UserRID =3D AuditedUsers.userRID
	   AND    auditedUsers.Customerrid =3D Customers.RID
	   AND    Customers.Bank =3D Banks.RID
	   AND    TRUNC(AuditTrail.DateTime) < (TRUNC(SYSDATE) -

NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD)); =20 begin

counter :=3D 0;
FOR Cur_DelAudit IN DelAudit_cur1 LOOP

      DELETE FROM AuditTrail a WHERE a.rid =3D Cur_DelAudit.RID;
      counter:=3Dcounter+1;

end loop;
dbms_output.put_line('counter=3D'||counter); end;
/

counter=3D12154

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.09
SQL> rollback;

Rollback complete.

new method 1:

declare

counter number;
begin

counter :=3D 0;
loop

      delete from AuditTrail =20
      where RID in (SELECT AuditTrail.RID
                    FROM    AuditTrail, Customers, Banks, auditedUsers
                    WHERE AuditTrail.UserRID =3D AuditedUsers.userRID
	              AND      auditedUsers.Customerrid =3D Customers.RID
	              AND      Customers.Bank =3D Banks.RID
	              AND      TRUNC(AuditTrail.DateTime) <

(TRUNC(SYSDATE)
- NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD)))

      and   rownum <=3D 1000;
      counter :=3D counter + sql%rowcount;
      exit when sql%rowcount =3D 0;   =20

end loop;
dbms_output.put_line('counter=3D'||counter); end;
/

counter=3D12154

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.01
SQL> rollback;

Rollback complete.

new method2:

declare

counter number;
begin

counter :=3D 0;
loop

      delete from AuditTrail  A
      where UserRID in (SELECT AuditedUsers.userRID
                        FROM   Customers, Banks, auditedUsers
                        WHERE  auditedUsers.Customerrid =3D Customers.RID
	                AND    Customers.Bank =3D Banks.RID)
      AND  (TRUNC(SYSDATE) - TRUNC(A.DateTime)) > (select
NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD)
                                                   from   Customers,

Banks,
auditedUsers

                                                   WHERE
auditedUsers.Customerrid =3D Customers.RID
	                                           AND    Customers.Bank

=3D
Banks.RID=20

                                                   AND
AuditedUsers.userRID =3D A.UserRID)
      and   rownum <=3D 1000;
      counter :=3D counter + sql%rowcount;
      exit when sql%rowcount =3D 0;   =20

end loop;
dbms_output.put_line('counter=3D'||counter); end;
/

counter=3D12154

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.03
SQL> rollback;

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

The information contained in this electronic message from Universal Tax Systems, Inc., and any attachments, contains information that may be confidential and/or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of this information is strictly prohibited. If you have received this communication in error, please notify Universal Tax Systems, Inc., immediately by e-mail or by telephone at 706/290-7200, and destroy this communication. Thank you.

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

Received on Wed Mar 23 2005 - 16:04:27 CST