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.
|
Sunday, July 15, 2012
Delete v/s Truncate
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment