Re: Update Statistics versus Recreate table after large purge

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