faster way of doing this?

From: Guang Mei <GMei_at_ph.com>
Date: Wed, 23 Mar 2005 15:36:40 -0500
Message-ID: <7E62D965D357694C993D4F0E13B3C39B04D38000@phexchange.ph.com>

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

http://asktom.oracle.com/pls/ask/f?p=4950:8:17490901823893610313::NO::F4950_ 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<=1000. 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 = AuditedUsers.userRID
	   AND    auditedUsers.Customerrid = Customers.RID
	   AND    Customers.Bank = Banks.RID
	   AND    TRUNC(AuditTrail.DateTime) < (TRUNC(SYSDATE) -

NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD)); begin

    counter := 0;
    FOR Cur_DelAudit IN DelAudit_cur1 LOOP

      DELETE FROM AuditTrail a WHERE a.rid = Cur_DelAudit.RID;
      counter:=counter+1;

    end loop;
    dbms_output.put_line('counter='||counter); end;
/

counter=12154

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.09
SQL> rollback;

Rollback complete.

• new method 1:

declare

    counter number;
begin

    counter := 0;
    loop

      delete from AuditTrail  
      where RID in (SELECT AuditTrail.RID
                    FROM    AuditTrail, Customers, Banks, auditedUsers
                    WHERE AuditTrail.UserRID = AuditedUsers.userRID
	              AND      auditedUsers.Customerrid = Customers.RID
	              AND      Customers.Bank = Banks.RID
	              AND      TRUNC(AuditTrail.DateTime) < (TRUNC(SYSDATE)
- NVL(Customers.AUDITRETENTIONPERIOD, Banks.AUDITRETENTIONPERIOD)))
      and   rownum <= 1000;
      counter := counter + sql%rowcount;
      exit when sql%rowcount = 0;    

  end loop;
  dbms_output.put_line('counter='||counter); end;
/

counter=12154

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.01
SQL> rollback;

Rollback complete.

• new method2:

declare

    counter number;
begin

    counter := 0;
    loop

      delete from AuditTrail  A
      where UserRID in (SELECT AuditedUsers.userRID
                        FROM   Customers, Banks, auditedUsers
                        WHERE  auditedUsers.Customerrid = Customers.RID
	                AND    Customers.Bank = Banks.RID)
      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)
      and   rownum <= 1000;
      counter := counter + sql%rowcount;
      exit when sql%rowcount = 0;    

  end loop;
  dbms_output.put_line('counter='||counter); end;
/

counter=12154

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

Received on Wed Mar 23 2005 - 15:41:06 CST