Erik,
First off, do the foreign keys have the 'on delete cascade' option turned
on? If not then do so as it makes keeping things in sync much easier. Actually
in this scenario you don't have to worry about the child tables.
Second, what do you mean by "The design of the application prohibits me from
adding indexes to these tables. "? I've not seen any application that
'prohibits' adding indexes.
Dick Goulet
____________________Reply Separator____________________
Author: Erik Williams <[EMAIL PROTECTED]>
Date: 11/1/2001 5:45 AM
I need to prune data from a set of tables every day. I need to retain the
last 90 days of information. Two of the tables, A and B, have foreign keys
to a third, C. I cannot disable the constraints prior to deleting the data,
because the system is 24/7. I have created a script that will delete the
data from each of the tables with foreign keys first, then from the parent
table. The problem I am having is the time it is taking to perform the
deletions. The A and B tables are without indexes on the foreign key,
because they very high volume insertion tables and very infrequent lookup.
These tables are very large. The design of the application prohibits me from
adding indexes to these tables.
Here is the code:
set serveroutput on
set timing on
DECLARE
id number(15);
dtm date;
cnt number;
cursor purge_c is
select id
from C
where dtm < sysdate-90;
BEGIN
open purge_c;
fetch purge_c into id;
cnt := 0;
while (purge_c%FOUND) loop
cnt := cnt + 1;
delete from A where id = id;
delete from B where id = id;
delete from C where id = id;
commit;
fetch purge_c into id;
end loop;
close purge_c;
DBMS_OUTPUT.PUT_LINE('Number of sessions deleted: ' || cnt);
END;
/
set timing off
set serveroutput off
I was thinking about creating another loop so that commits will only be done
every 1000 deletions, but I think that the commits are a very a small
percentage of the time compared to the table scans. I also considered
partitioned tables, but I really don't want to go to that length. I was
hoping to hear how other people handle this issue.
Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
