Truncate table vs drop table and fragmentation

Truncate table vs drop table and fragmentation

Post by Doug Smit » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Truncate table vs drop table and fragmentation

Post by Jerry Gitome » Sun, 31 Dec 1899 09:00:00


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


>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


 
 
 

Truncate table vs drop table and fragmentation

Post by Pete Sharma » Sun, 31 Dec 1899 09:00:00


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


> 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


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

  psharman.vcf
< 1K Download
 
 
 

Truncate table vs drop table and fragmentation

Post by Connor McDonal » Sun, 31 Dec 1899 09:00:00



> 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


> >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
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"

"Some days you're the pigeon, and some days you're the statue."

 
 
 

Truncate table vs drop table and fragmentation

Post by Jonathan Genni » Sun, 31 Dec 1899 09:00:00


Quote:>We have a DBA in the office, however, who tells me I'm an idiot

Us DBAs are good at that :-).

Quote:>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.

Offhand, I'm not convinced that this is true, though it
might be. Consider that the first time through, you created
the tables and populated them with data. Dropping and
recreating the tables, and repopulating them, should yield
more or less the same results the second time as the first.

If you were truly worried about fragmentation, you would
check the max size of the tables after populating them, and
then recreate them once with an initial extent of that size.

Quote:>I haven't seen any performance degradation or encountered any problems with
>creating new extents, so I'm a bit skeptical.

Then don't worry about it. I spend my time chasing problems
that bother my users, not the theoretical things. Well, I
chase the theoretical things too, but they are much lower
down on the priority list. I think fragmentation is
overrated as a problem, and that people worry about it more
than they should.

If you do want an idea of how badly fragmented things are,
you can query the dba_extents view (you may have to use
all_extents or user_exents) like this:

        select count(*)
        from dba_extents
        where owner='XXX'
        and segment_name='table_name'
        and segment_type='TABLE';

This will give you a count of the number of extents that
exist for a table. If it's only a half-dozen or so, don't
worry about it. If you have 500 extents, then you should
probably do something about it.

regards,

Jonathan

p.s. why isn't your DBA helping you on this?

_____________________________________________________

http://gennick.com
Brighten the Corner Where You Are

 
 
 

1. Modify to truncate vs. drop table

I've never bothered to test it, but DROP always seems to work pretty much
instantly.  If there is a difference it ain't worth worrying about.  I
think your programmer is confusing droping the table with emptying it.  A
MODIFY TO TRUNCATED is MUCH faster than a DELETE FROM...

========================================================================

BKB Engineering Ltd.,                      NAIUA Member-at-Large
11211-76th Avenue,                
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================

2. Stupid replication question that Microsoft support can't answer

3. Problem with Drop Table and truncate table

4. CLARION: Please follow up!

5. Drop table versus truncate and drop index

6. Pick operating system

7. Truncate table vs delete table

8. Copy of recordset FIELDS

9. truncate table vs delete table

10. Drop Vs (Truncate & Drop)

11. Disk fragmentation caused by creation and drop of table in a user database

12. drop any table vs. delete any table

13. Unable to drop or truncate table