Tuesday, November 17, 2009

Which is faster Truncate/Delete

Truncate is faster than Delete
When a Delete operation is executed the data that needs to be deleteded is copied to Rollback table space first, that is the reason the data can be rolled back.
When Truncate is called the row data is completely removed, Hence Truncate is faster than delete

DELETE,TRUNCATE and DROP

Difference between Delete, Truncate and Drop
Delete - is used to remove few/all the rows of a table. Transaction needs to be COMITTED or ROLLBACKED explicitly to make permanent changes. This operation will fire the DELETE triggers

TRUNCATE is used to remove all the rows from a table. This operation will not fire the DELETE triggers. Operation cannot be rolled back.

DROP is used to remove the table from the database. All rows,indexes and privileges will be removed.Operation cannot be rolled-back.