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
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<=1000. 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 = AuditedUsers.userRID AND auditedUsers.Customerrid = Customers.RID AND Customers.Bank = Banks.RID AND TRUNC(AuditTrail.DateTime) < (TRUNC(SYSDATE) - NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD)); begin counter := 0; FOR Cur_DelAudit IN DelAudit_cur1 LOOP DELETE FROM AuditTrail a WHERE a.rid = Cur_DelAudit.RID; counter:=counter+1; end loop; dbms_output.put_line('counter='||counter); end; /
counter=12154
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.09 SQL> rollback;
Rollback complete.
-- new method 1:
declare counter number; begin counter := 0; loop delete from AuditTrail where RID in (SELECT AuditTrail.RID FROM AuditTrail, Customers, Banks, auditedUsers WHERE AuditTrail.UserRID = AuditedUsers.userRID AND auditedUsers.Customerrid = Customers.RID AND Customers.Bank = Banks.RID AND TRUNC(AuditTrail.DateTime) < (TRUNC(SYSDATE) - NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD))) and rownum <= 1000; counter := counter + sql%rowcount; exit when sql%rowcount = 0; end loop; dbms_output.put_line('counter='||counter); end; /
counter=12154
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.01 SQL> rollback;
Rollback complete.
-- new method2:
declare counter number; begin counter := 0; loop delete from AuditTrail A where UserRID in (SELECT AuditedUsers.userRID FROM Customers, Banks, auditedUsers WHERE auditedUsers.Customerrid = Customers.RID AND Customers.Bank = Banks.RID) AND (TRUNC(SYSDATE) - TRUNC(A.DateTime)) > (select NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD) from Customers, Banks, auditedUsers WHERE auditedUsers.Customerrid = Customers.RID AND Customers.Bank = Banks.RID AND AuditedUsers.userRID = A.UserRID) and rownum <= 1000; counter := counter + sql%rowcount; exit when sql%rowcount = 0; end loop; dbms_output.put_line('counter='||counter); end; /
counter=12154
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.03 SQL> rollback;
Rollback complete.
************************************************************************* 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
