Re: this table won't clean itself

In article <54o73a$phf_at_news.inconnect.com>, <ldalton_at_inconnect.com> writes:
> Path: news.ou.edu!news.nodak.edu!netnews1.nwnet.net!netnews.nwnet.net!reuter.cse.ogi.edu!uwm.edu!cs.utexas.edu!howland.erols.net!news.sprintlink.net!news-peer.sprintlink.net!uunet!in3.uu.net!news2.thoughtport.net!news.inconnect.com!news
> From: ldalton_at_inconnect.com (Larry Dalton)
> Newsgroups: comp.databases.oracle
> Subject: this table won't clean itself
> Date: Thu, 24 Oct 1996 17:02:05 GMT
> Organization: Internet Connect, Inc. -- http://www.inconnect.com
> Lines: 26
> Message-ID: <54o73a$phf_at_news.inconnect.com>
> Reply-To: ldalton_at_inconnect.com
> NNTP-Posting-Host: slc-dial-23.inconnect.com
> X-Newsreader: Forte Free Agent 1.0.82
>
> I have a table that has been created like this:
>
> (Oracle 6)
>
> create table events(id number,
> event_number number,
> event_cnt number,
> event_tm date,
> event_str long)
> storage (initial 2m next 1m)
> PCTFREE 1;
> grant all on events to PUBLIC;
> create index E2 on events(id)
> storage (initial 2m next 1m)
> PCTFREE 1;
>
> This table contains from 100,000 to 500,000 records. I holds up to
> two days worth of event informtion... nightly, it deletes the previous
> days messages. When the table is fresh, this nightly clean up is fast
> (1-2 hours), however, over time, this cleanup takes longer and longer,
> until finally, this table crashes the machine because it runs out of
> extents and won't add any more records (even though there is plenty of
> room for more records). I was hoping to find a solution to this
> problem without droping and re-creating the table or index. Does
> anyone have any ideas?
>
>

Well, It appears to me that you will need to bite the bullet on this one. If I understand correctly, You only delete the oldest days information and you keep the new day. and you only store two days at any given time. The problem is DELETES do not refresh you High Water Mark (I think this is part of you Table Header Info). The only way to reclaim this space is eather to truncate the table or drop and recreate it.

It sounds like the record count is reletively small. (I said RELETIVELY) AND i cant spell. You seem to have a handle on the criteria needed to identify the records you need to keep, so their are several options.

1. Delete your record, export, drop table, and import.
2. create a new temporary table and copy records you want to keep to it, then drop old table and recreate it and copy records back.
3. You could also maintain two seperate tables on for each day and join them in a view, that way truncating is fast.
4. You could keep two tables and copy back and forth only needed information. then change synonims or use view names.

I know this is not all of your options but you do have a problem. Maybe one of these sounds appealling. I would like to here what you end up doing.

Martin Douglas Received on Fri Oct 25 1996 - 00:00:00 CEST