faster way of doing this? 2005-03-23 - By Reidy, Ron
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
http://asktom.oracle.com/pls/ask/f?p=3D4950:8:17490901823893610313::NO::F=
4950_
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.
*************************************************************************=
=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.
--
http://www.freelists.org/webpage/oracle-l
|
|
