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@(protected) [mailto:oracle-l-bounce@(protected)] 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 (See Ora-01555.ora-code.com): 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
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; /
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.
