RE: faster way of doing this?
- From: Guang Mei <GMei@xxxxxx>
- To: "Oracle-L (E-mail)" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 23 Mar 2005 17:01:10 -0500
I settled with "new method 1", but added a hint in the subquery, that sped
up the whole thing so that now the new method is taking the same amount of
time the current code does.
Thanks for all your responses.
Guang
-----Original Message-----
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx]
Sent: Wednesday, March 23, 2005 4:16 PM
To: Ron.Reidy@xxxxxxxxxxxxxxxxxx
Cc: GMei@xxxxxx; Oracle-L (E-mail)
Subject: Re: faster way of doing this?
I fully agree with the rowid - although indeed the temp table is not
necessary. Besides, I believe that rewriting
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)
into
AND A.DateTime <
(select TRUNC(SYSDATE) - NVL(Customers.AUDITRETENTIONPERIOD,
Banks.AUDITRETENTIONPERIOD) + 1
from Customers, Banks, auditedUsers
WHERE auditedUsers.Customerrid = Customers.RID
AND Customers.Bank = Banks.RID
AND AuditedUsers.userRID = A.UserRID)
would be beneficial if DateTime is indexed, as it can be expected to
be. It's hard to say that without any knowledge of volumes but
intuitively I would say that the right thing to do in the above subquery
is a full scan of auditedUsers.
SF
Reidy, Ron wrote:
>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@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]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: 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.
>
>
*************************************************************************
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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
