Erik - Another suggestion that has been mentioned on this forum is to
consider partitioning. You could partition by insertion date, with each
partition holding a month of data. Then you could clean up by truncating the
oldest partition. Without indexes, any use of these tables will probably be
table scans, so you may receive a performance bonus for queries that only
need data within a certain date range.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, November 01, 2001 10:00 AM
To: Multiple recipients of list ORACLE-L
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: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> 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 <[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).
--
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: DENNIS 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).
