Database technique – Mass DELETE vs TRUNCATE

• Finds simple improvements by replacing mass deletes of rows in tables.

• The goal of this detection is to reduce the consumption and the duration of the processes affected.

Detection

In Oracle there are two kinds of commands that can delete the rows of a table: DELETE and TRUNCATE. These two commands are meant for different situations: DELETE is used to remove part of a table's data, while TRUNCATE is used to empty the table completely. Although they are different, since DELETE is considered a data manipulation command and TRUNCATE a data definition command, the first can get the same result as TRUNCATE when no filters are applied when it runs, although in a far less optimal way.

Using DELETE to empty a whole table can be a performance problem, because it goes through a series of intermediate steps that consume resources on the database instance. These steps include a full scan of the table, finding the blocks that hold the rows, adjusting the table's indexes and generating redo so that the delete can be undone if the transaction fails, among others. All these steps considerably increase the time and resources needed to empty a table.

When the whole table is to be emptied, it is much better to use TRUNCATE, because it resets the table's high-water mark to zero and marks the space the table used as empty. This way a table can be emptied completely in less time and with less resource consumption, reaching the same goal in an optimised way. Note that this delete is final: the data stored cannot be recovered, either by rollback or by flashback.

Comparison and results

Following the good-practice guide built into our methodology, we have applied this specific technique at our clients. For a table with more than 7 million rows, where each row was deleted one by one, we reduced the run time (elapsed) by 99% and CPU consumption by more than 95% by using TRUNCATE instead of DELETE:

In conclusion, if we need to empty a table whose data we know is no longer needed, using TRUNCATE is much more efficient: it consumes no extra resources and the run time is considerably shorter.