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@(protected)] Sent: Wednesday, March 23, 2005 4:16 PM To: Ron.Reidy@(protected) Cc: GMei@(protected); 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@(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. > >
************************************************************************* 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
