What is the difference between TRUNCATE and DELETE commands

Oracle Apps Interview Questions › Category: SQL › What is the difference between TRUNCATE and DELETE commands
0 Vote Up Vote Down
Questions Master asked 10 years ago

which operation is faster to perform? and why?

1 Answers
0 Vote Up Vote Down
Shailender Thallam Staff answered 10 years ago
  1. DELETE is DML, TRUNCATE is DDL statement
  2. TRUNCATE is much faster than DELETE Reason: DELETE statement copies all the records to ROLLBACK TABLESPACE and then deletes the records row by row (thats the reason you can get back all the deleted records when you use a ROLLBACK command immediately after DELTE command. where as TRUNCATE directly deletes the records without copying it to ROLLBACK TABLESPACE.
  3. You can’t ROLLBACK in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
  4. In case of TRUNCATE ,Trigger doesn’t get fired.But in DML commands like DELETE. Trigger get fired.
  5. You can’t use WHERE clause in TRUNCATE but we can use in DELETE statment.
  6. Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster
  7. Truncate requires an exclusive table lock, delete requires a shared table lock.
  8. Delete does not recover space, Truncate recovers space (unless you use the REUSE STORAGE clause)