Re: Fragmentation and TRUNCATE vs. DROP

"grz02" <grz01_at_spray.se> wrote in message news:1641ee43.0403180320.6cb8185a_at_posting.google.com...
> I have seen this claim in a few places that for Fragmentation reasons,
> it is better to do TRUNCATE TABLE than DROP TABLE,
> but I havent seen any explanation why that would be so.

Tablespace fragmentation is what you get when you have lots of pockets of free space within a tablespace, but none of the pockets are big enough to satisfy the NEXT extent requirements of segments seeking to extend. Therefore, the only way to suffer from fragmentation is (a) to have segments with different sized extents within a tablespace and (b) to free up some of those odd-sized extents. If you didn't do (a), but instead made all segments within a tablespace have exactly the same sized extents, then dropping or truncating a table would leave behind lots of "holes" of free space, but those holes would all be of a size that the surviving segments would want to occupy in future. If you didn't do (b) then even with wildly odd-sized extents, you'd never suffer from fragmentation, because you wouldn't be creating the "holes" of free space in the first place.

Drop and truncate both free up extents, therefore both, in conjunction with (a), cause tablespace fragmentation. A truncate might reasonably claim to cause rather less fragmentation than a drop because truncates never free up the initial extent of a segment (or the MINEXTENTS quantity of extents if that's been specified) whereas a drop frees up all extents regardless.

But the difference would be utterly piffling, and I wouldn't lose any sleep over it.

> Anyone knows whether this is true, and if so, what the difference
> would be?

See above. It would be trivial.

> Here is one such text, but I have seen it mentioned somewhere else,
> too:
>
>

http://www.orapub.com/cgi/genesis.cgi?p1=dwnld&bogus=zip_out&p2=na&p4=paper&p3=doc121.zip

I didn't read the original article, because you have to be a member to do so. You might bear that in mind next time you recommend a text.

> "The dropping issue can also be reduced with the proper storage
> parameter management (i.e., creating fewer extents) and eliminated by
> simply truncating the table"

Any indication in the text as to when this particular gem was written? Because it's pretty ancient advice these days. The whole idea of controlling fragmentation by creating fewer extents went out the window with the introduction of uniform sized locally managed tablespace. With US-LMTs, every extent is the same size for all segments within a given tablespace, and therefore fragmentation can never, ever happen. Doesn't matter how many extents you've got.
The advice would therefore appear to be coming from the dark ages of version 8.0 or before.

In any case, the advice is completely wrong, since truncate frees up almost as many extents as a drop (usually, at any rate, MINEXTENTS apart), and therefore would cause nearly as much fragmentation as a drop. The idea that a truncate "eliminates" the problem is complete and utter nonsense.

Regards
HJR Received on Thu Mar 18 2004 - 05:53:25 CST