What is the difference between delete and truncate?

TRUNCATE is a DDL statement. It issues auto-commit after the exectution of the statement.

BUT DELETE keeps records in buffers(temporary storage) till the first ROLLBACK or COMMIT. You can use conditions on DELETE like WHERE but  You can't using TRUNCATE.

and trancate for data size = zero

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 is DDL command and Delete is DML command.

Truncate not maintains log and Delete command does.

Truncate cannot rollback but Delete can rollback data. 

Hi,

Delete operation simply mark the data to be removed from the extent/data block.

Delete operation is logged into redo log file hence same can be rollback.

However the truncate which is DDL operation its simply remove the data and free up the Data block /Data extent. it faster and can not rollback.

DELETE operations can be rolled back, while TRUNCATE operations cannot be rolled back .

DELETE operations can be rolled back, while TRUNCATE operations cannot be rolled back as it is DDL command. DELETE can be used to remove all or few records from a table using where clause. Where as TRUNCATE is used to remove all the records in a table.

delete can be used to delete particular row r colunm.

truncate can be used to delete overall table perminently.

Thanks a lot , clear answer

1>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

Truncate is a DDL command with auto-commit  and no selection (all data removed) and can not be reversed whereas delete is a DML command require a commit to confirm deletion + selection of records in where clause can be added to delete command.