Re: Truncate table vs drop table and fragmentation

Hi Doug,

    Since DBAs never make misteaks you obviously didn't hear what your DBA really said.

    In terms of fragmentation there is no difference between DROP and TRUNCATE. Simplistically speaking, the difference is that both TRUNCATE and DROP get rid of the data and at that point TRUNCATE quits but DROP goes on to delete the table and index related entries from the Dictionary.

    TRUNCATE resets the high water mark so that it points to the beginning of the initial exent allocated to the table. Effectively all of the space associated with the table, except for the initial allocation, is released.

    You, and your DBA, can avoid the fragmentation problem altogether by using a default storage clause for each tablespace and then not overriding it in the table create statements. This will result in everything in a tablespace being the same size, ergo you can not have fragmentation -- excessive extents perhaps, but fragmentation no.

regards
Jerry Gitomer

Doug Smith wrote in message ...
>Greetings, Oracle experts.
>
>I have an Oracle 7.3.4 installation I use for development. We've
been doing
>some longevity testing on our app, and in between runs I need to
clean out
>the database.
>
>The easiest way for me to do this is to drop the tables and
recreate them.
>We have a DBA in the office, however, who tells me I'm an idiot
for doing
>this since I'll fragment the tablespaces, and that I should
truncate the
>tables instead. Unfortunately, he is unable to articulate why
this would
>cause fragmentation.
>
>I haven't seen any performance degradation or encountered any
problems with
>creating new extents, so I'm a bit skeptical.
>
>Can anyone explain this to me, or point me to the appropriate
Oracle
>documentation.
>
>Thanks
>
>--Doug
>
>
Received on Wed Jul 21 1999 - 12:00:16 CDT