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.

Depends. If your table is huge you're probably better off unloading what you want to keep (both a back up of the whole thing and what you want to retain), drop the table, create the table, and load with a bulk loader; e.g., dbload. If you want to retain the entire original (as a table), unload what you want in a new one, rename the old table, create the new table and bulk load. If you simply delete unwanted rows you'll have to alter an index to clustered (or vice versa) to reclaim the space, and that requires at least 2x the dbspace (probably more) -- the amount of dbspace you have available will help you decide which way to go. When I have to do this once or twice a year I unload a back up (just on general principals), unload what I want, drop, create, and load. Doesn't take that long to do and I start with a "clean" table that's contiguous. 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. > >

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