Organizational Research By

Surprising Reserch Topic

Question:how to Compare and contrast TRUNCATE and DELETE for a table?


 

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.
 

asked Sep 13, 2013 in ORACLE by rajesh
edited Sep 12, 2013
0 votes
54 views



Related Hot Questions

6 Answers

0 votes

 

Compare and contrast TRUNCATE and DELETE for a table ?
 
Answer is:
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete. 
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013
0 votes

 

 
 
I’ve answered this question many times, and answered it again this weekend.  What is the difference when doing a DELETE TableA instead of TRUNCATE TableA? 
A common misconception is that they do the same thing.  Not
so.  In fact, there are many differences between the two.
 
DELETE is a logged operation on a per row basis.  This means
that the deletion of each row gets logged and physically deleted.
 
You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.
 
TRUNCATE is also a logged operation, but in a different way. 
TRUNCATE logs the deallocation of the data pages in which the data
exists.  The deallocation of data pages means that your data
rows still actually exist in the data pages, but the
extents have been marked as empty for reuse.  This is what
makes TRUNCATE a faster operation to perform over DELETE.
 
You cannot TRUNCATE a table that has any foreign key
constraints.  You will have to remove the contraints, TRUNCATE the
table, and reapply the contraints.
 
TRUNCATE will reset any identity columns to the default seed
value.  This means if you have a table with an identity column and
you have 264 rows with a seed value of 1, your last record will have
the value 264 (assuming you started with value 1) in its identity
columns.  After TRUNCATEing your table, when you insert a new
record into the empty table, the identity column will have a value of
1.  DELETE will not do this.  In the same scenario, if you
DELETEd your rows, when inserting a new row into the empty table, the
identity column will have a value of 265.
 
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013
0 votes
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013
0 votes

 

truncate is faster then delete... truncate don't generate 
rollback. truncated data can't be recovered
 
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013
0 votes

 

Truncate is to delete the entire data of any table .does not 
have criteria deletion
where as Delete can do that job
However Deleted blocks still persists and have to be 
shrinked .otherwisem You still feel the Deleted Recs while 
you Query Against
 
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013
0 votes

 

DELETE
 
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
 
SQL> SELECT COUNT(*) FROM emp;
 
  COUNT(*)
----------
        14
 
SQL> DELETE FROM emp WHERE job = 'CLERK';
 
4 rows deleted.
 
SQL> COMMIT;
 
Commit complete.
 
SQL> SELECT COUNT(*) FROM emp;
 
  COUNT(*)
----------
        10
 
TRUNCATE
 
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
 
SQL> TRUNCATE TABLE emp;
 
Table truncated.
 
SQL> SELECT COUNT(*) FROM emp;
 
  COUNT(*)
----------
         0
 
DROP
 
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
 
SQL> DROP TABLE emp;
 
Table dropped.
 
SQL> SELECT * FROM emp;
SELECT * FROM emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
 
From Oracle 10g a table can be "undropped". Example:
 
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
 
Flashback complete.
 
PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

 

answered Sep 13, 2013 by anonymous
edited Sep 12, 2013 by rajesh

...