Agreed. I have timed this out and come up with a number more like 1/10th of
the table, but I did not consider rebuilding indices or constraints in that
time. However, if you are deleting a lot of rows, consider that you are
leaving huge holes of deleted rows in your table - space which will
eventually be reclaimed, but not as efficiently were you to re-write the
table - in which case you leave no holes. In other words you will get
limited performance boost from deleteing a lot of rows - where you would get
a more substantive performance boost by re-writing it.
cheers
j.
----- Original Message -----
From: "Art S. Kagel" <kagel@bloomberg.net>
To: <informix-list@iiug.org>
Sent: Wednesday, January 28, 2004 7:16 PM
Subject: Re: Update Statistics versus Recreate table after large purge
> On Wed, 28 Jan 2004 10:07:24 -0500, B.Johnson wrote:
>
> > 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.
>
> Depends. If you will be purging more than about 1/3 of the data and the
> table is large, then B will tend to be faster. But do not forget to
recreate
> indexes and constraints and then UPDATE STATISTICS.
>
> If this is a dependent table with FOREIGN keys then the cost of validating
> the CONSTRAINT will shift the costs back towards A.
>
> Art S. Kagel
>
sending to informix-list