Sunday, July 15, 2012

Delete v/s Truncate


DML (Delete)
DLL (Truncate/Drop)
Remove row one by one
Remove row in one execution and fast
Creates a roll back statement
Don’t create any log so cannot be rolled back
Allows conditional remove of record (where clause etc can be used )
Doesn’t allow conditional remove of record (all records have to be removed )
Triggers are fired
Triggers are not fired
Make entries in log file
Doesn’t make entries in log file
When you type DELETE.all the data get copied into the Rollback Tablespace first, then delete operation get performed.That's why 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.
In TRUNCATE,it removes data directly without copying it into the Rollback   Tablespace.That's why TRUNCATE is faster.Once you Truncate you can't get back the data.
You can use DELETE statement on a parent table and if CASCADE ON DELETE is enabled then data from child table also get deleted. If CASCADE ON DELETE is NOT enabled and any of the child table has related then you can’t delete records from parent table.
You can’t truncate a parent table irrespective of whether the child table has any record or not. Truncate table statement will fail for parent table even if CASCADE ON DELETE is enabled.
Does not reset identity value of the table
Reset identity value of the table.
You may use DELETE statement against a view (with some limitations).
You can’t use TRUNCATE statement against a view.


No comments:

Post a Comment