Differentiate between TRUNCATE and DELETE

TRUNCATE deletes much faster than DELETE

TRUNCATE

DELETE

It is a DDL statementIt is a DML statement
It is a one way trip,cannot ROLLBACKOne can Rollback
Doesn't have selective features (where clause)Has
Doesn't fire database triggersDoes
It requires disabling of referential constraints.Does not require
  • Interview Candidate
  • 21
  • 3765

Showing Answers 1 - 21 of 21 Answers

shiva

  • Sep 3rd, 2005
 

Once we Use Truncate we can't get the data back. But if we use Delete we can undo the data  
using rollback.

  Was this answer useful?  Yes

Saravanan

  • Oct 19th, 2005
 

shiva Wrote:Once we Use Truncate we can't get the data back. But if we use Delete we can undo the data
using rollback.
High Water Mark of the table back to zero,High water mark is not affceted in delete .

  Was this answer useful?  Yes

Sampath

  • Oct 22nd, 2005
 

Truncate deletes all the records at one go, delete statement can delete specific rows or all rows.

Truncate is DDL and delete is DML

  Was this answer useful?  Yes

prasanna

  • Dec 3rd, 2005
 

Truncate       delete

it remove        delete keep the tablespace 

table space

  Was this answer useful?  Yes

sreelakshmiI

  • Feb 16th, 2006
 

delete is a dml statement

truncate  is a ddl statement

delete is mainly used to delete the records truncate is used to dele the table completel y but structure remains same.

we can get back the deleted recods by using roll back command whereas rollback does not work for truncate

  Was this answer useful?  Yes

Rana

  • Apr 5th, 2006
 

Truncate and Delete are used for deleting data

Difference is

  • Auto commit in Truncate. No auto commit in Delete.
  • Truncate is faster when compare to delete.
  • Truncate -> High Water Mark set to ZERO

  Was this answer useful?  Yes

vin

  • Aug 24th, 2006
 

If you have delete triggers, they will be fired when we run the delete command, but not with Truncate

  Was this answer useful?  Yes

sajin

  • Sep 24th, 2006
 

in delete the table structure also go but in truncate the table structure will be remain.
regards
sajin

  Was this answer useful?  Yes

reks

  • Jan 8th, 2007
 

truncate is a DDL statement.Delete ia DML statement

Truncate works faster than delete as data need not be written to rollback segments.

Trunacte cannot be rolled back,delete can be rolled back

Truncate releases storage space,delete doesnt release storage space

Truncate does not work when foriegn key is enabled

Delete trigger does not fire on truncate

Truncate requires DELETE TABLE privilege if the user does not own the table

  Was this answer useful?  Yes

sayeed321in

  • Apr 12th, 2007
 

Hi guys.....
Note one more thing.....we can recaptulate the data even after the Delete and a COMMIT using the DBMS_FLASHBACK ....the same thing is not possible with the truncate command because the memory is no longer active.

Please come back if anything new is found on this topic.

Thanx & Regards,
Ahmed Sayeed

  Was this answer useful?  Yes

Shaveta Chawla

  • Sep 7th, 2007
 

One main freature is when we have used auto increment clause the would return to next value after delete statement. e.g: if id =1000
then after delete statement when we insert any record the value will be 1001 but if we use truncate statement then auto increment value will come to 0 positon

  Was this answer useful?  Yes

amarparash

  • Sep 27th, 2008
 

DELETE is a DML command so it works on row level, where TRUNCATE is a DDL command, so it works on object level, TRUNCATE actually deletes the table and recreates it, so whatever people say that it works faster or frees occupied space or high water mark or oil mark.The actual internal work was that.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions