Re: Truncate table vs drop table and fragmentation

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Thu, 22 Jul 1999 20:48:43 GMT
Message-ID: <37a47f9f.27320715@netnews.worldnet.att.net>

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

Us DBAs are good at that :-).

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

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

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are Received on Thu Jul 22 1999 - 15:48:43 CDT