1. Insert the rowids you want to delete into a global temp table 2. delete from "real" table where rowid in gtt
-- ---- ---- ---- Ron Reidy Lead DBA Array BioPharma, Inc.
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On Behalf Of Guang Mei Sent: Wednesday, March 23, 2005 1: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; /
counter=3D12154
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.03 SQL> rollback;
Rollback complete.
*************************************************************************= =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
This electronic message transmission is a PRIVATE communication which = contains information which may be confidential or privileged. The information is = intended=20 to be for the use of the individual or entity named above. If you are = not the=20 intended recipient, please be aware that any disclosure, copying, = distribution=20 or use of the contents of this information is prohibited. Please notify = the sender of the delivery error by replying to this message, or notify us = by telephone (877-633-2436, ext. 0), and then delete it from your system.
