Archiving serveral large tables

Last Post 01 Mar 2007 03:19 PM by nosepicker. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

01 Mar 2007 03:19 PM
If you're going to do a straight delete, you may want to consider first dropping all your indexes, except the ones necessary for the actual delete statement. It's usually the updating of the indexes that takes up the bulk of the time in a large delete statement. Naturally, it will take time to recreate the indexes that you dropped, but I've found that this method is sometimes faster. Also, if you're going to delete a significant part of your table (say more than 20% of it), you may want to consider inserting the data that you want to keep into a separate table, creating the indexes on it, dropping the old table, and then renaming the new table with the old table's name. I've often found this to be faster, and it's also safer because you can still retain your original data for a while in the event of a mistake.

Acceptable Use Policy