Re: Fragmentation and TRUNCATE vs. DROP

grz02 wrote:
>
> 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.
>
> Anyone knows whether this is true, and if so, what the difference
> would be?
>
> 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
>
> "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"

Normally papers from OraPub are pretty good. But this paper is old and outdated when you consider today's LMT environments. The paper also has some inaccuracies. For instance, the paper says "only dropping an object will create TFF", that is Tablespace Freespace Fragmentation. But truncating a table, thus releasing extents, will do the same thing. The above quote is inaccurate as well. Dropping a table and truncating a table both release extents. Dropping releases all extents. Truncating releases all but the INITIAL extent. It is assumed that one is not truncating with REUSE STORAGE.

This paper is so outdated, that if I was using LMTs, I would completely disregard Section 3, Tablespace Freespace Fragmentation.

One should keep in mind that papers tend to be written for that time period. Newer technologies make some points invalid or moot. Further research can also make the information in a paper invalid, as we all learn more about the technologies at hand.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"