Re: DELETE vs TRUNCATE

Deletes do normal DML. That is, they take locks on rows, they generate redo (lots of it), and they need rollback segments. They clear records out of blocks carefully. You can roll the command back. No space is relinquished by the segment -it still possesses exactly the same number of blocks after the delete as before.

Truncates cheat. They move the High Water Mark of the table back to ground zero, and that's pretty much all they do. No row-level locks, no redo, and no rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, 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, unilke with deletes, of course).

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Stjepan Brbot" <Stjepan.Brbot_at_ZG.HiNet.HR> wrote in message
news:a3rof4$458u$3_at_as201.hinet.hr...


> What is the difference between DELETE and TRUNCATE SQL commands? What's

> about indicies of tables which content is emptied by those commands?

>

> --

>

> Stjepan Brbot <Stjepan.Brbot_at_ZG.HiNet.HR>

>

>

>