Re: Truncate table vs drop table and fragmentation

One additional comment. The effect of the TRUNCATE command can be different to the DROP command if you specify REUSE STORAGE. This isn't the default though.

HTH. Pete

Jerry Gitomer wrote:

> 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
> >
> >

--
Regards

Pete

Received on Wed Jul 21 1999 - 12:16:47 CDT