RE: Data Deletion in Tables with Foreign Keys

That is an excellent suggestion! I had not thought of that.

Those tables are populated with data as a result of web site interactions. Inserts need to be as fast as possible. There were no indexes added due to the overhead of maintaining an associated index.

Thanks again for the suggestion. I am going to look into that this afternoon.

Erik

> -----Original Message-----
> From: dgoulet_at_vicr.com [SMTP:dgoulet_at_vicr.com]
> Sent: Thursday, November 01, 2001 10:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:Data Deletion in Tables with Foreign Keys
>
> 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
> --------------------------------------------------------------------
> 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: 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).