Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

What is the difference between DELETE and TRUNCATE commands?

user-image
Question ajoutée par Muhammad Shahid Farooq , Software Engineer, System Integrator , ForeVision Business Solutions
Date de publication: 2017/03/06
Akhil Parvatkar
par Akhil Parvatkar , Senior Software Engineer , Citigroup Global Services Pvt. Ltd.

TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause

Anjum Rizwi
par Anjum Rizwi , Senior Technical Leader , Valtech India

DELETE is DML command, when we run this query all affected recorded in log. Since it is recorded we can rollback. 

 

Truncate is DDL command. when we run this command it de-allocates the memory that is allocated to this table. System can't record the activity in log. So roll back is not possible. If you have foreign key reference you can't run truncate because system can't check the dependency. 

Muhammad Waqar Arif
par Muhammad Waqar Arif , Senior Database Architect , FINCA Microfinance Bank Limited

Delete is DML command and it can generate on delete entry and record write in log file so that will help to recover.

 

Truncate is DDL command, It removes all rows from table instead of specific one with Where clause like we use in Delete.It is faster command and didn't use space as much as Delete.

More Questions Like This

Avez-vous besoin d'aide pour créer un CV ayant les mots-clés recherchés par les employeurs?