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)
5 comments:
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.
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.
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.
let me share my experience with regard to the service of sql data recovery, it automatically eliminates data corruption issues in selected databases
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.
Post a Comment