RE: faster way of doing this?

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Wed, 23 Mar 2005 13:56:02 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B43947E@fiji.arraybp.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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.

*************************************************************************=

=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

Received on Wed Mar 23 2005 - 15:58:00 CST