Re: number of extents question

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Thu, 12 Sep 2002 09:12:07 +0200
Message-ID: <alpeuc$1rfm94$1@ID-152732.news.dfncis.de>

Hi, Yong Huang,

"Yong Huang" <yong321_at_yahoo.com> schrieb im Newsbeitrag news:b3cb12d6.0209111348.4f0c32c3_at_posting.google.com...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:<x9wf9.29168$g9.83917_at_newsfeeds.bigpond.com>...
> > But would I rebuild a table if it had 10 or 20 or 100 extents. No.
>
> I agree. But I remember Jonathan Lewis seems to say if the number of
> extents exceeds 50 or 100, rebuilding the segment may be warranted. I
> don't have the book here so I may be misreading him.
>
> By the way, I never alter table/index coalesce, and I always use
> compress=n in export. I'd like to hear others' opinion.

For Tables, we export them, make a COALESCE of the TS and import them again. compress=n makes sense for uniform extent sizes ... i never tried it before,
but I may do it soon.

For Indices, I do

ALTER INDEX my_index
REBUILD TABLESPACE TS_IND
STORAGE (
   INITIAL 1M
   NEXT 1M
   PCTINCREASE 0
   MAXEXTENTS 999999
)
COALESCE
/
ALTER TABLESPACE TS_IND COALESCE
/

after every rebuild. I do not know if COALESCE could be _overdone_, too, but I had a lot of
fragmentation solved by this successfully.

Jan Received on Thu Sep 12 2002 - 02:12:07 CDT