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

There are several ways to do this. I have researched this.

Best way to achieve COALESCE of the contiguous fragmented space is to set pctincrease =1 for tablespace where the indexes or tables are maintained. If you do
this Oracle SMON process will automatically defragment the space after deletions/inserts as it wakes up.

Only problem is you will have to use Alter tablespace command and it will COALESCE only the newer segments.

To achieve COALESCE for all disk space you will have to drop and recreate tables and indexes. It took SMON several minutes, as much as 15 to 20 minutes to COALESCE approximately 100 MEG of disk space.

Good Luck !!!

Connor McDonald <mcdonald.connor.cs_at_bhp.com.au> wrote in article <350E3E15.56FA_at_bhp.com.au>...
> 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."
>
Received on Tue Mar 24 1998 - 00:00:00 CST