Re:Data Deletion in Tables with Foreign Keys

From: <dgoulet_at_vicr.com>
Date: Thu, 01 Nov 2001 07:02:33 -0800
Message-ID: <F001.003BA53B.20011101071020@fatcity.com>

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 <ewilliams_at_brownco.com>
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: ewilliams_at_brownco.com

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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------