IDS Forum

Re: Massive delete - any ideas to do it nondisruptively?

Posted By: Preetinder Dhaliwal
Date: Thursday, 19 June 2003, at 9:53 a.m.

In Response To: Re: Massive delete - any ideas to do it nondisruptively? (Obnoxio The Clown)

How abt a trigger on delete of master table which will delete from children and
then using detach fragment? I dont know if informix will allow that, but you can
try

Rgds
Preetinder

"Obnoxio The...." wrote:

> Clever soul? Anyway, have you considered fragmenting the tables on a
> timestamp range, then detaching the oldest fragment and dropping the
> detached table?
>
> It's very fast and minimally disruptive. Plus, it may even improve
> performance overall... :o)
>
> --
> Bye now,
> Obnoxio
>
> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> - Coluche
>
> >From: iiug@dcw.mailshell.com
> >To: ids@iiug.org
> >Subject: Massive delete - any ideas to do it nondisruptively? [1386] Date:
> >Wed, 18 Jun 2003 10:52:13 -0400 (EDT)
> >
> >I'm looking for some clever soul out there to help me with what should be a
> >well-solved problem by now. (If not, it's our chance to make history!)
> >
> >The situation is fairly classic: I have several large tables, one parent
> >and five children, tied together by a serial number. The parent table, but
> >not the children, has a timestamp. These are active tables, averaging
> >about 100,000 rows per day in the parent table and more in the children.
> >When the data gets old enough, the rows get deleted.
> >
> >Question: How can I purge old data, based on the timestamp, from these
> >tables NONDISRUPTIVELY?
> >
> >I know I can use HPL to unload the data I want to keep, drop the table, and
> >reload it. That works but it's disruptive.
> >
> >Obviously, I can just delete the relevant information, either in a single
> >cascading delete or by a joined delete for the children and a ranged one
> >from the parents. That works, too, but it's slow.
> >
> >I could copy the data I want to keep to new tables, drop the originals and
> >rename the copies. The problems there are that it's still a little
> >disruptive (though tolerably so) and the data in the tables being copied is
> >being updated during the copy, so keeping synchronization would be hard.
> >The fatal flaw in this plan, though, is I don't have enough disk space to
> >copy the tables.
> >
> >The idea has been advanced of fragmenting the parent table based on the
> >date and time, detaching the fragment with the data to be purged, then
> >dropping the resultant new table. That works great for the parent (maybe,
> >if indexes don't have to be rebuilt), but how do I do the children? I have
> >no control over the serial numbers being used.
> >
> >So......... anyone out there have any great ideas for running these massive
> >deletes as a lights-out, nondisruptive process? (IDS 7.31.FC6, HP/UX
> >10.20)
> >
> >-Don Wolford, Verizon Data Services
> >
> >
> >_______________________________________________________
> >The FREE service that prevents junk email http://www.mailshell.com
> >
>
> _________________________________________________________________
> Express yourself with cool emoticons - download MSN Messenger today!
> http://www.msn.co.uk/messenger

Messages In This Thread

• Massive delete - any ideas to do it nondisruptively?
  iiug@dcw.mailshell.com -- Wednesday, 18 June 2003, at 10:52 a.m.
  • RE: Massive delete - any ideas to do it nondisruptively?
    John Carlson -- Wednesday, 18 June 2003, at 11:47 a.m.
  • Re: Massive delete - any ideas to do it nondisruptively?
    Obnoxio The Clown -- Wednesday, 18 June 2003, at 7:27 p.m.
    • Re: Massive delete - any ideas to do it nondisruptively?
      Preetinder Dhaliwal -- Thursday, 19 June 2003, at 9:53 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.