Truncate and Replace

Trying to delete more than 10% of the rows in a huge table? Rather than deleting the rows you don't want, it might be faster to:

• Copy the rows you want to keep into a temporary table using Direct Load Insert
• Truncate the base table
• Copy the temporary table back into the base table with Direct Load Insert

The technique also works if you are trying to update more than 10% of the rows in a table.

• Copy all rows into a temporary table using Direct Load Insert
• Truncate the base table
• Insert the rows from the temporary table back into the base table using a SELECT caluse to modify the value of the columns that need to be updated. This SELECT can perform table joins etc to derive the new values.

Deletes and Updates are very slow compared to Direct Load Inserts. The actual break-even point beyond which the techniques above will out-perform Delete and Update will probably be somewhere around 10% to 50% of the table/partition, but will depend greatly on the indexes on the table which must be rebuilt after a Direct Load Insert.