Re: Update Statistics versus Recreate table after large purge
If you have the luxury of fragmenting your data in this fashion then this is
an optimal solution. Your re-org is then a function of seconds, not
minutes/hours. I used this successfully to replace a yearly purge process
which was written in esql/c something along the lines of:
select * from table
if select_date < purge_date then
delete from table
This to run against a 40GB table, I was not foolish enough to ever attempt
testing this. I did laugh/cry a lot tho.
The detach fragment / attach new fragment worked exceptionally well. It
also allowed us to archive off the deleted data at our leisure.
However, fragmenting your data for the benefit of a periodic purge process
means that your day-to-day operations cannot have perhaps a more optimal
fragmentation scheme. The table we used it for was a transaction history
table where there was a limited need to ever actually read the data back.
cheers
j.
----- Original Message -----
From: "Colin Bull" <c.bull@videonetworks.com>
To: <informix-list@iiug.org>
Sent: Friday, January 30, 2004 5:29 AM
Subject: RE: Update Statistics versus Recreate table after large purge
> I thought the best way to do this was try and fragment you data on a
function of the deletion, ie, if you purge your data every
> month, fragment over 12 dbspaces.
> Then just drop the one to be purged.
>
> I have promised myself I am going to test this real soon.
>
> Colin Bull
> c.bull@videonetworks.com
>
> > -----Original Message-----
> > From: owner-informix-list@iiug.org
> > [mailto:owner-informix-list@iiug.org]On Behalf Of B.Johnson
> > Sent: 28 January 2004 15:07
> > To: informix-list@iiug.org
> > Subject: Update Statistics versus Recreate table after large purge
> >
> >
> > Quick question as to what is the most effective and expediant way to
> > handle a large table purge. Should we ...
> >
> > A) run the purge and then run Update Stats with Drop Distributions.
> >
> > or
> >
> > B) make a copy of the table ... run sql to get records you want to
> > keep ... verify data ... rename old table ... rename new table ...
> >
> > Time is of essence here ... as far as execution.
> >
>
> sending to informix-list
sending to informix-list