faster way of doing this? 2005-03-23 - By Justin Mitchell
That commit you took out is pretty important, it's the whole point of
doing the statement this way, and it's the whole point of Tom's answer
(and the original question). =20
Tom says:
"The second loop keeps NO cursors open across commits. That is why is
avoids the 1555. It is a cursor opened against a table that you are
modifying in a loop and you commit frequently that causes the 1555."
And=20
"It is when you comit across fetches that you do this to yourself."
What he means is that if you're going to do commits in your loop, you
need to pull a new dataset afterwards to avoid your error. That's what
the error is telling you too (since it's trying to get a consistent
read).
Based on what I see, your old code works faster because it's pulling the
dataset once, whereas the other code is pulling it 13 times, each time
getting a different dataset to delete. But you're negating any
performance benefits by not committing because you're still generating
essentially the same amount of rollback.
This method will be slower than doing a single bulk commit or even a
single loop just due to the fact that you are pulling the dataset more
often, but if you need to do that for your rollback segments, then you
may not have much of a choice.
If you do end up doing it this way, experiment a bit and see how high
you can get your "rownum<=3DX" part. The less number of times you run the
statement, the less time it will take.
Thanks,
Justin Mitchell
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Guang Mei
Sent: Wednesday, March 23, 2005 3: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::F4
950_
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.
http://www.freelists.org/webpage/oracle-l
The information contained in this electronic message from Universal Tax Systems
, Inc., and any attachments, contains information that may be confidential and
/or privileged. If you are not the intended recipient, you are hereby notified
that any disclosure, copying, distribution or use of this information is
strictly prohibited. If you have received this communication in error, please
notify Universal Tax Systems, Inc., immediately by e-mail or by telephone at
706/290-7200, and destroy this communication. Thank you.
--
http://www.freelists.org/webpage/oracle-l
|
|
