Hi

can any one tell me the difference between delete and truncate statement in mysql . which is better to use?

Hi,
This is Veera Reddy
The difference between delete and truncate is.
if u use delete, it will delete row by row and after deletion there is a possibility of rollback your data where as if you use truncate it will delete the entire table i.e., with structure and after deletion it will recreate the structure and there is no possibility of rollback of data once you apply truncate.

Just wanted to add one more point.

Truncate is a DDL command and Delete is a Dml command.

DDL statements perform commit implicitly. That is why your changed are committed automatically when ever you perform a DDL statement ( Create,Alter etc).

DML statements does not commit your changes. You have to commit explicitly inorder to save your changes. Examples of DML commands are insert,update, delete etc.

__________________
Cheers,
:) James:)

Good one from veera_KK and jamesravid

Add one more point also

When Delete the Particular row the Corresponding Delete Trigger(if exists) Fire.
In Case of Truncate the Trigger is not fired.

Simply to say -

DML statements (Delete) can be rollbacked where DDL (Truncate) are autocommit.

__________________
Sanghala
www.dwforum.net - A Great resource for Data Warehousing Professionals

If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

'truncate' requires no condition bcoz it deletes the total table once where as 'delete' requires condition bcoz it deletes the data row by row.....

Truncate is a DDL command and Delete is a DML command. Hence Truncate operation can not be rolled back, where as delete operation can be rolled back. where clause can be used with delete command and not with the truncate command.

Both delete and truncate will delete the rows in a table. But there are some differences between them

1. Delete will delete rows on the table based on the condition, either may be a single row or many rows
eg., Delect * from tablename where condition;
Truncate will delete all the rows in the table.

2. Truncate is a DDL command but Delete is a DML command.

3. Rollback is possible after the delete command, but since truncate is a ddl command rollback is not possible.

hi
i want to add one more point
truncate is faster then the delete and doesn't use as much undo space as delete

if u delete a record from a table the deleted record will be stored in log file.u can retrieve it later.But its not in the case of truncate.it will delete frm the log file also

Since Delete is DML it deletes only the data.
Truncate is DDL it changes the table structure it self, which is not allowed while another user is connected to the table.

Delete
At the simplest level, delete scans the table and removes any rows that match the given criteria in the (optional) where clause. It generates rollback information so that the deletions can be undone should it be necessary. Index entries for the deleted rows are removed from the indexes. You must commit to make the deletions permanent.

When deleting rows from a table, extents are not deallocated, so if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. In addition the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may (still) take a long time to complete - because a full table scan always scans up to the HWM. So, by example, if a select count(*) from very_large_table; took 15 minutes to complete before all the rows were deleted, you will find that it still takes about 15 mins after the deletion - because Oracle is still scanning every single block up to the HWM - even though some (or all) of the blocks may have no data in them.

Truncate
Truncate, on the other hand, simply moves the high water mark on the table right down to the beginning. It does this very quickly, and does not need to be committed. Once you truncate a table, there is no going back. Indexes are also truncated. There is no facility to be able to specify which rows to 'delete' as you can with the where clause on the delete command.

When a table is truncated, all its extents are deallocated leaving only the extents specified when the table was originally created. So if the table was originally created with minextents 3, there will be 3 extents remaining when the tables is truncated.

If you specify the reuse storage clause, then the extents are not deallocated. This saves time in the recursive SQL department if you intend to reload the table with data from an export for example, and can reduce the time it takes to do the import as there is no need to dynamically allocate any new extents.

with delete operation one can rollback the data; this shows after performing delete operation , data is not completely deleted;

but in case of trancate , one cannot retrive the data using rollback operation....................

trancate delets the data permanently; ie one cannot perform rollback operation on the trancated data.........

in case of delete one can retrive the data the deleted data using rollback operation.......................

truncate does not take any data to the rollback segment or undo tablespace.
truncate is fast.
truncate delete's the data permanently.

delete will copy the data to the undotablespace for rollback.
delete is slow.

hi,
delete removes data in the table

but truncate deletes the structure of the table.

delete means it will delete all rows.and space of deleted rows will not be released.
but when truncate the table all the rows will be deleted & space of rows also deleted.
when deleting we can roll back but in trucate roll back is not possible.