rajaramian
Answered On : Feb 8th, 2006
We can ROLLBACK the DELETE statement but we can not ROLLBACK the TRUNCATE statement
Login to rate this answer.
Delete generates rollback segments, and its DML
Truncate does not generate rollback segment and its DDL
once Truncate does not generate rollback so its faster then delete
Login to rate this answer.
prince maruthi
Answered On : Feb 9th, 2007
truncate=DELETE+COMMIT;
delete=delete+rollback;we can rollback after the delete command ,then we can get the deleted data
Login to rate this answer.
Delete
Delete remove the Data only, the Table structure remains intact.
This is a DML Statement
Rollback possible
No Commit is performed neither before nor after. (Because it is a DML Statement).
They take locks on rows,
They generate redo (lots of it)
They require segments in the UNDO tablespace.
A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
A truncate does not move the High Water Mark of the table back to zero, it retains its original position.
Delete deletes the specific rows filtered by where statement. and log is maintained for it.
It can activate the triggers.
Truncate
Truncate remove the Data only, the Table structure remains intact.
This is a DDL Statement
Rollback not possible (Except in SQL 2005)
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)
No row-level locks are taken.
No redo or rollback is generated.
They do not require segments in the UNDO tablespace.
All extents bar the initial are de-allocated from the table
A truncate moves the High Water Mark of the table back to zero
Truncate deletes the page associated with the table so all indexes are reset
It does not activate the triggers.
Login to rate this answer.
sheel
Answered On : May 17th, 2007
Hi friends
In truncate command deletion of row happen but one important thing is that, after truncate memory space is also empty or you can say that operation going on memoryelement is deleted from memory permanetly. that is why they cannot rollback.
But in case of delete commandthe element is still present in memoryso memory is not empty after the delete operationso they can rollback after deletion. thanx
if any one has new logic plz send me
Login to rate this answer.
1) Rollback : Not possible in Truncate but possible in Delete because it
Delete uses the rollback Segments.
2) Trigger : If we have a Delete Trigger, it will not be fired in case of
Truncate.
3) Type : Delete is a DML Type of statement but Truncate is a DDL statement.
4) Speed : Truncate is much faster than Delete because it does not use the
rollback segments
5) High Water Mark : The High water mark of the table is set to 0 in case of
Truncate but this variable retains its original value in Delete.
Login to rate this answer.
1. Truncate is a DDL command where as Delete is DML.
2. There is no rollback for Truncate where as for Delete we can rollback.
Login to rate this answer.
TRUNCATE - Truncate is a ddl command.
DELETE- Delete is a dml command.
TRUNCATE - We can not use 'where' clause with truncate.
DELETE- We can use 'where ' clause with delete .
Login to rate this answer.
Truncate is faster than delete as it does not generate rollback segment.
Login to rate this answer.
