Oracle DBA Question:
Download Questions PDF

What is the difference between Truncate and Delete? Why is one faster?
Can we ROLLBACK both? How would a full table scan behave after?

Answer:

Truncate is nearly instantaenous, cannot be rolled back, and is fast because Oracle simply resets the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary row in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely slow.

Download Oracle DBA Interview Questions And Answers PDF

Previous QuestionNext Question
What is your favorite tool for day-to-day Oracle operation?What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?