A few basic notes on Oracle Database Administration.

Sunday, September 28, 2008

Delete vs. Truncate - graphically

This is too funny to not share. Of course, everyone knows that a truncate is much more efficient than a full table delete. On my system, still suffering from a slow log writer, but absolutely quiet at the moment, I ran an insert of a million rows (ctas from tbl1 to tbl2), a delete of those million rows (from tbl2) , and then a truncate of them (from tbl1). I looked over at Enterprise Manager and this is what I saw: (I added the tags for benefit of blog readers)















I had not been expecting such a graphical re-enforcement of the rule. I had found the full table delete in some executing code and was curious how much redo it was generating. I ran René Nyffenegger's script 'how_much_redo' .
Assuming that it is giving accurate results, here is what I got for the 3 operations.

SQL> INSERT INTO T1_DEL (SELECT * FROM T1);

1161874 rows created.

SQL> exec how_much_redo;
New Redo Bytes Written: 408985600 (390 MB of redo TO INSERT)


SQL> delete from T1_DEL;

1161874 rows deleted.

Elapsed: 00:06:30.90 (6 MINUTES TO DELETE)
New Redo Bytes Written: 661496320 (630 MB of redo TO DELETE)



SQL> truncate table T1;

Table truncated.

Elapsed: 00:00:06.80 (6 SECONDS TO TRUNCATE)
New Redo Bytes Written: 815616 (less than 1Mb of redo to TRUNCATE)

6 comments:

Steve said...

Um, of course truncating an *empty* table will be faster than deleting records ... You need to do

1) insert
2) delete
3) insert
4) truncate

and then you can compare delete versus truncate times.

Steve G.

girlgeek said...

Steve,
The table that I truncated was the one that had the million rows in it originally. Notice the insert
INSERT INTO T1_DEL (SELECT * FROM T1);
I then delete from t1_del and
truncate from t1. It was not an empty table.

colin said...

Another point - I don't think TRUNCATE can be wrapped in a transaction. So you might not always have the luxury of changing a DELETE FROM to a TRUNCATE. But point taken.

Anonymous said...

let me share my experience with regard to the service of sql data recovery, it automatically eliminates data corruption issues in selected databases

sap support packs said...

Yes I already know the differences between both these commands. And you have explained the efficiency of truncate statement so nicely by sharing the image and example with the help of which a beginner can easily understand it at the very first glance.

Lalit Raghuvanshi said...

11 main Differences between Delete and Truncate in sql server
http://www.webcodeexpert.com/2013/03/difference-between-delete-and-truncate.html