Re: Q:Table Fragmentation. How do I reduce it?

Rao Uppuluri wrote:
>
> Hi all
>
> Setup: Oracle 7.3.2.3 on HP-UX 10.20
>
> I have a table(s) with lot of extents allocated to it. These extents are
> small extents. The extents allocated are not all next to each other.
> Some are but not all. If I drop the table and recreate it, do I get a
> lot of "free space fragmentation"? How do I reduce the fragmentation of
> the table? Do I have to export and import the whole tablespace?? ( I have
> many of these tables with lot of extents allocated to them in the tablespace)
>
> Thanx in ADvance
>
> Rao Uppuluri
> (uppuluri_at_XidtX.net -- Please remove X's)

Also worthy of note that in a normal (ie multi-user) environment then as a general rule, lots of extents will NOT affect performance. The popular belief that the performance of one extent is always better than many is a myth...

This is not to say the exp/imp will not assist. Unloading and reloading the data has many benefits in terms of better packing the rows etc etc...But exp compress=y OR exp compress=n will give you the same benefits...

-- 
==========================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad."