What is the difference between DELETE and TRUNCATE?
Answer Posted / brajendra arzare
Deletes perform normal DML. That is, they take locks on
rows, they generate redo and they require segments in the
UNDO tablespace. Deletes clear records out of blocks
carefully. If a mistake is made a rollback can be issued to
restore the records prior to a commit.
Truncates are DDL and truncate moves the High Water Mark
of the table back to zero. No row-level locks are taken, no
redo or rollback is generated.so they have the same effect
as a delete, but without all the overhead. Just one slight
problem: a truncate is a DDL command, so you can't roll it
back if you decide you made a mistake. (It's also true that
you can't selectively truncate -no "WHERE" clause is
permitted, unlike with deletes, of course).
Is This Answer Correct ? | 13 Yes | 1 No |
Post New Answer View All Answers
What is normalization in a database?
How do I truncate a sql log file?
how do you control the max size of a heap table? : Sql dba
What is insert command in sql?
what are the performance and scalability characteristics of mysql? : Sql dba
Can we enter data in a table in design view?
what is a constraint? Tell me about its various levels. : Sql dba
how to rename an existing table in mysql? : Sql dba
how to shutdown mysql server? : Sql dba
What is scalar data type in pl sql?
What is meant by truncate in sql?
How does sql profiler work?
What is fmtonly in sql?
Can dml statements be used in pl/sql?
Which is better join or inner query?