Difference between truncate and delete

  • Interview Candidate
  • 28
  • 6436

Showing Answers 1 - 28 of 28 Answers

Nagabhushan S N

  • Jun 9th, 2006
 

TRUNCATE

It's a DDL command and delete the complate table data and no rollback possible.It also resets the HIGH WATERMARK which will imporve subsequent query performance.

DELETE

This is DML statement and require commit to make changes permanant.Can be used to delete selective rows from table.Does not reset the High Watermark of the table.

Vithal Patil

  • Jun 23rd, 2006
 

TRUNCATE

It is possible to Delete data permanantly from the table without commit. 

After deletion  don't have rollback option.

DELETE

Require commit to make changes permanantly after delete command.

and also u can select particular row to delete with where conditions.

here after deletion have rollback option

  Was this answer useful?  Yes

D.Madhu

  • Jun 29th, 2006
 

Delete

With Rollback Facility

DML Operation.

No Auto Commit.



Truncate

Without Rollback Facility

DDL Operation

Auto Commit

  Was this answer useful?  Yes

harshali patil

  • Jun 1st, 2007
 

Truncate:
truncate require no commit
Delete:
delete require commit

  Was this answer useful?  Yes

krishnaindia2007

  • May 7th, 2008
 

1. Delete creates a rollback segment and cane be rollback. It is DML statement.  Truncate is a DDL statement and can not be rollback.

2. Delete command deletes records one by one where as truncte removes all the records at a time.

3. Truncate does not allow conditional remove of records where as delete allows conditional remove of records

4.Trigger fires on delete statement where as trigger do not fires on truncate statement.

cajie

  • Oct 26th, 2009
 

More difference are as below:


1) TRUNCATE fires 2 COMMIT before firing and after deleting the rows. The COMMIT fired before, commits all the open uncommitted transactions.

2) TRUNCATE does not write into redo/undo logs which makes it faster than DELETE.

Correct me if I am wrong.

Cajie

  Was this answer useful?  Yes

geek.hari

  • Dec 28th, 2009
 

Delete will need an explicit commit or rollback to end the transaction. It will perform a redo log entry.
Truncate has inherent commit in it. It will not perform any redolog entry.

  Was this answer useful?  Yes

Gourvendra Singh

  • Oct 9th, 2012
 

Major Differences are:
Delete: Delete command will delete either full table records or make a conditional deletion on the table.
Truncate: It will delete all the records of the table.
Delete: Delete will write the records in redo log buffer before deleting the records and erase the entry from the redo log buffer once the transaction is committed or rollback.
Truncate: Truncate will delete the records directly from the table and records cannot be rollbacked. This is why truncate runs faster then delete.
Truncate: It will reset the watermark of the table.

  Was this answer useful?  Yes

jose george

  • Oct 15th, 2012
 

in addition to others

truncate is DDL hence would implicitly commit before executing
Delete is DML
truncate resets the High water mark
delete does not reset the high water mark

  Was this answer useful?  Yes

VASU

  • Oct 20th, 2012
 

By using delete we have to delete particular records only..
If we use truncate we cannot retrieve the data permanently...

  Was this answer useful?  Yes

Hasan

  • Dec 15th, 2012
 

truncate is a ddl...delete is dml
cannot rollback data in truncate...rollback data in delete
truncate -delete entire data not structure...it delete specific datas

  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