Home : Products : OPS SQL : Remove all records from a huge table without writing transaction log
Q10450 - HOWTO: Remove all records from a huge table without writing transaction log

Problem :
Running query that deletes all rows from a table in Oracle or Microsoft SQL can take extraordinary amounts of time and disk space on huge tables. Query such as

delete from calcerrorlog

takes time because transaction log entry is written for EACH row within the table. If you take a table with 50 million rows for instance, it is a lot of log entries. The log is also persisted to disk, which means a query like that can grow the transaction log file and several gigabytes. depending on the width of the table it may be on the order of 10's of gigabytes.

HOWTO Solution : Use TRUNCATE TABLE command instead. Such as

truncate table calcerrorlog

This function writes one transaction log entry, and is basically instantenous. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
Consult documentation for both Oracle and MSSQL on how to use it. There are a few gotcha's that you have to be aware of, such as

  • TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
  • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
  • TRUNCATE TABLE may not be used on tables participating in an indexed view.
  • etc...

Once again, consult documentation for your current version of DBMS before using this.

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
http://msdn2.microsoft.com/en-US/library/aa260621(SQL.80).aspx
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10007.htm#SQLRF01707
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 9/11/2007 9:41 AM.
Last Modified on 9/11/2007 9:51 AM.
Last Modified by No Author Name Available!.
Skill Level: Advanced.
Article has been viewed 7429 times.
Rated 8 out of 10 based on 4 votes.
Print Article
Email Article