Friday, February 25, 2011

To Truncate or Delete, Is it Really a Question?

One of the primary objectives of an oracle DBA is to protect the data that is stored in the database. The potential for data loss can come from many different threats and an often overlooked one is from a user accidentally deleting data. By implementing proper security mechanisms, the DBA can define the proper access to prevent unauthorized users from having the ability to delete data from tables that shouldn't be accessing. However, there will occasions when a developer will need to delete data for testing purposes or when working with custom tables. Defining this access is fairly straight-forward as well, but there can also be situations when providing additional access can actually be beneficial. If a developer needs to delete all the data in a table, they have two options: they can issue a regular DELETE statement or they can use an Oracle command called the TRUNCATE command. Now most DBAs will get very nervous at the thought of allowing a user to truncate tables because there is more risk involved when this privilege is in the wrong hands. However, consider this scenario we recently encountered:

User A needs to delete data from a custom table and issues "DELETE FROM T;"
The table T in question is 50 GB in size, so behind the scenes what does Oracle have to do for this "simple" statement? The database must keep track of all the rollback information for the records being deleted in the UNDO area in case the user decides to rollback his changes or the statement fails. The modifications to the table generates change records in the database redo logs that Oracle uses to keep track of the sequence of changes made to the database and also copies these files to archive redo logs as they fill up. All of the indexes on table T must be maintained to keep track of the record pointers, which also generates redo log activity.

After running for 2 hours, the delete statement finally failed when it consumed all of the available UNDO space because of the amount of changes being made. The change generated so much activity that it also almost completely used all the available disk space on the server where the archive logs are stored. Once the transaction failed, then Oracle had to reverse the changes to put the data back the way it was. During this rollback process that took an additional 2 hours, Oracle generated additional redo activity from reverting the changes. All of this to delete data that isn't needed anymore and meanwhile the system resources are being consumed, potentially impacting activity that end users are trying to complete.

Now compare this to the effect of issuing a TRUNCATE on the same table. Truncating table T completed in a matter of seconds, consumes no UNDO space, and generates no redo activity. The truncate command also reclaims the space used by the table by resetting the high water mark (a marker Oracle uses to track the used blocks in a table). If the delete command had completed successfully, table T would still be 50 GB in size unless additional maintenance was performed to reclaim the unused free space. Truncate doesn't come without risks, the biggest being that it cannot be undone. When issuing a delete statement, the user has a chance to verify they deleted the intended data before issuing a commit (save) or rollback (undo) statement. With a truncate, there is no opportunity for Oracle to ask the user "Are you sure?" so this must be used with caution.

Careful consideration should be used when allowing access to the TRUNCATE command. The elevated privileges required by the TRUNCATE command will allow the user to truncate any table in the database; however the potential for reduced impact on the system can often outweigh the security risk in non-production databases. Hopefully this example shows how using the features that are available in Oracle can help save your system from unnecessary problems.

No comments:

Post a Comment