| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Truncate table vs drop table and fragmentation
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
> >
> >
or as some sites I have seen...have several tablespaces (all with a default size) so that they can have "big" objects in the big-default-size tablespace, "medium" objects in the medium-default-size tablespace etc etc...
Then no fragmentation as you say...and moreover, no tables in hundreds or thousands of extents...
Cheers
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Jul 21 1999 - 08:42:51 CDT
 |
 |