Which one is faster DELETE/TRUNCATE? Why?

  • Interview Candidate
  • 85
  • 64106

Showing Answers 1 - 75 of 85 Answers

kalyan

  • Oct 11th, 2005
 

becase truncate canot rollback

  Was this answer useful?  Yes

Praveen

  • Oct 11th, 2005
 

Truncante is performs better than delete because when you delete the records from the database, database has to perform 2 actions.

                1.delete from the database

                2.write the deleted records into "rollback" segments.

     But incase of "Truncate" the second activity is not required.

Cheers,

Praveen.

  Was this answer useful?  Yes

sandeep vig

  • Oct 13th, 2005
 

truncate is faster than delete bcoz truncate is a ddl command so it does not produce any rollback information and the storage space is released while the delete command is a dml command and it produces rollback information too and space is not deallocated using delete command.

successgs

  • Nov 17th, 2005
 

Truncate is more faster than delete. Since it has a power of releasing the structure of table storage size and deallocates whereas delete is used to mere deletion of records in tables with the usage of where clause as a optional one. Truncate is also fall under DDL part.

  Was this answer useful?  Yes

suresh

  • Nov 29th, 2005
 

Hi,

Truncate is very faster than delete becuase

1.no need to write any date in redo log files

2.no need to fire any triggers

Suresh.D

  Was this answer useful?  Yes

MelJos

  • Dec 12th, 2005
 

A TRUNCATE statement can be rolled back if it is performed within a transaction. The speed of the TRUNCATE statement itself testifies that the actual data is not erased untill it's rewritten. So it is possible to rollback A TRUNCATE Statement.

  Was this answer useful?  Yes

alagusundaram

  • Feb 23rd, 2006
 

Truncate is a DDL comment and we cannot rollback. Truncate issue a commit before and after performing the task.

Sudeep Biswas

  • Nov 9th, 2006
 

TRUNCATE is faster than DELETE because truncate does not logs the rows that are deleted where as DELETE keeps a record of rows deleted till the transaction is committed.This is the reason why data that is truncated is non recoverable.

  Was this answer useful?  Yes

Iwein

  • Jan 19th, 2007
 

    Truncate is faster than delete because it just resets the high water mark. Delete performs operations on each record (delete, rollback segmen..). The downside is that once a table is truncated all the space it used before is immediately released. Once the truncate is executed the space taken up by the table before is free and can be used by any other session. Hence no commit is needed, no rollback is possible and you can only delete all data not a subset.
Essentially truncate drops the whole data part of the table. Whereas delete removes elements from the data one by one.

  Was this answer useful?  Yes

Vaibhav

  • Apr 3rd, 2007
 

Below is the best suitable reson:

1. Truncate is an autocommit transaction; therefore as soon as this is executed the database is commited.
2. Delete is a forced-commit transaction which gives luxury of rollback.

Therefore once we want to delete all the records of any table we use truncate instread of delete which will be much faster.

Regards,
-Vaibhav

  Was this answer useful?  Yes

Guest

  • Apr 10th, 2007
 

Because when delete is given, it update the data dictionary and generated redo while that is not the case with truncate.

Thanks,

Tina K.

  Was this answer useful?  Yes

Sivaguru

  • Apr 17th, 2007
 

Truncate is faster than delete because it deletes memory space.

  Was this answer useful?  Yes

rajendra_thanekar

  • May 4th, 2007
 

Truncate table
------------------
1) Truncate table is DDL and can be run by the owner of the table.
2) Can't Rollback the truncate table command.
3) It release the space and re-set the high water mark in the segment.
4) Selective record deletion is not possible with truncate table . i.e you can not specifiy where condition in truncate table command.

Delete
--------
1) User with delete permission can delete records from the table.
2) Delete can be rollback.
3) Does not release the occupied space in the segment.
4) We can specifiy selective record deletion in delete  i.e. delete from <table_name> where <condition>


Regards

Rajendra Thanekar.

Dutai

  • Jul 30th, 2007
 

      1. Truncate
            It is faster than delete becoz it is a DDL command so it does not produce any rollback information and the storage space is released.
      2.  Delete 
            It  is a DML command and it produces rollback information too and space is not deallocated using delete command.

  Was this answer useful?  Yes

parag tyagi

  • Sep 27th, 2007
 

Truncate is faster because in case of truncate oracle don't create images in rollback segments i.e. it reduces the time of rollback segments

  Was this answer useful?  Yes

rajendra_thanekar

  • Oct 20th, 2008
 

Truncate is faster than delete statement because delete statement generate redo while truncate generate minimum redo. Delete can be rollback truncate command can not be rollback. Truncate command reset high water mark in the segment and release the space while delete does not reset the high water mark.

  Was this answer useful?  Yes

malathi123

  • Nov 25th, 2008
 

The DELETE command is used to remove selected rows from a table using WHERE clause . If we donot use  WHERE condition then all the  rows will be removed. After performing a DELETE operation  we need to COMMIT or ROLLBACK the transaction to make the changes permanent or to undo it. Delete  operation will cause all DELETE triggers on the table to fire.

TRUNCATE removes all the rows from a table. We cannot rolled back this operation and no triggers will be fired. 

TRUCATE is faster and doesn't use as much undo space as a DELETE.

  Was this answer useful?  Yes

rajendra_thanekar

  • Apr 22nd, 2009
 

Truncate and delete both are used to delete the records from the table. The difference is

Truncate never fire delete trigger placed on the table.
Truncate resets the high water mark for the table and will not generate any redo.
Truncate can not be recoverable.
Only owner of the table can truncate a table.

Delete fires the delete trigger placed on the table.
Delete uses undo segment to store the records which are deleted so that if you rollback the delete you will get the records back.
Delete never resets the high water mark of the segment.
User with delete permission can delte records from the table.

  Was this answer useful?  Yes

Ravi Maggon

  • May 16th, 2011
 

truncate is better because it drops the table which is faster then deleting every entry and then re creates the table with same structure.

  Was this answer useful?  Yes

madhu

  • Jul 26th, 2011
 

truncate is very faster then delete, because truncate is DDL command.. so it will not allow commit and rollback,by using truncate function we can delete complete data in the table, it will not allow where condition, so we can't delete particular record...it will release space

  Was this answer useful?  Yes

rajini

  • Jul 26th, 2011
 

Always TRUNCATE is faster than delete as Truncate directly removes data and space allocated to it from table where as delete will search for condition given in where clause and then delete the data but remains the space allocated to it . DDL is faster than DML. TRUNCATE is a DDL language and DELETE is DML Language

  Was this answer useful?  Yes

murugan

  • Aug 10th, 2011
 

truncate is better because it drops the table which is faster then deleting every entry and then re creates the table with same structure.

  Was this answer useful?  Yes

Ataur Rahaman

  • Oct 11th, 2011
 

truncate is faster than delete because delete query must require where clause that means it require the entire table search for a particular where clause and delete the particular record or records .........
but as no such where clause is used in truncate..its require no such entire scan hence time for execute truncate is much more lesser so truncate is faster
-Ataur Rahaman

  Was this answer useful?  Yes

rajarao

  • Jan 3rd, 2012
 

Yes while deleting records Oracle writes each and every operation in log files so for this Oracle takes much time to delete records and updating log file simultaneously, that is we can rollback all the deleted records from database. Truncate deletes all the records along with the allocated space for those record so truncate is very fast but we cant be rolled back.

  Was this answer useful?  Yes

aishwarya

  • Jan 24th, 2012
 

Truncate will be faster than delete .because delete will have an copy of deleted item where us truncate will not have a copy then delete is use to delete from the particular form and truncate will delete it from the whole structure

Code
  1. SELECT tablename,TRUNCATE COLUMN name;

  Was this answer useful?  Yes

Babu Lal Roy

  • Dec 24th, 2012
 

1) Delete is a DML statement and it generate redo log entry. Truncate is a DDL statement and it does not generate redo log entry.
2) Truncate reset the high water mark to release space consumed by table or its partitions.
3) On DML all the dependent triggers is fired but in in Truncate it doesnt.

This is the reason truncate is faster than delete.

  Was this answer useful?  Yes

Bharath

  • Feb 18th, 2014
 

Truncate will be faster than delete. Because truncate wont make any backup of deleted rows where in delete it will take backup of deleted records for purpose of rollback. In other words Truncate will work on original data of database but incase of delete it will work on copy of original object, it made changes in database once get the commit instruction from user.

  Was this answer useful?  Yes

SQL Guy

  • Aug 21st, 2014
 

This is crazy .. the original question was posted in 2005 and people are still answering through 2014 with the same answers, come on guys whats wrong with you.

By the way all the ones saying that you cant roll back a truncate you are wrong. It can be rolled back without a problem.

SQL guy

  Was this answer useful?  Yes

deep

  • Feb 2nd, 2015
 

Truncate is faster in comparison to Delete because truncate delete all the rows in a single shot whereas Delete will remove rows one by one

  Was this answer useful?  Yes

Ashish

  • Feb 10th, 2015
 

Truncate will remove the watermark along with the records in the database, however Delete statement will remove the records only. It means when you use the Delete statement, it only removes the records but memory used by those records in the table will remain unchanged.

Also, Truncate is a DDL statement and Delete is a DML statement, hence Truncate doesnt require a COMMIT or ROLLBACK.

  Was this answer useful?  Yes

Gopika Anjan

  • Mar 23rd, 2015
 

Truncate is faster becz its auto commit and the it doest not copy data to undo TBS as it is otherwise in case of Delete, when delete cmd is issues the data is forst coped to undo TBS as it is not auto commit.

  Was this answer useful?  Yes

manideep

  • Jul 13th, 2016
 

Truncate removes all records in table where as delete removes record by record from table.

Rollback cannot be done on truncate because it is implicit commit where rollback takes place on delete.

  Was this answer useful?  Yes

shaik

  • Sep 7th, 2016
 

DELETE is a Safe mode of operation as it can be ROLLBACKed whereas TRUNCATE is Faster operation but cannot be ROLLBACKed.
DELETE can be Few Records based on WHERE Clause while the TRUNCATE is a Full operation.

  Was this answer useful?  Yes

Harishankar Sahu

  • Nov 24th, 2017
 

Truncate is faster. Because, its a DDL. So, no rollback information is stored. Truncate also does not consider integrity constraints; while delete does.

  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