Re: number of extents question

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:alpeuc$1rfm94$1_at_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.

Gawd! What a lot of work. And for no good reason, too! I suppose there's a reason you haven't switched to Locally Managed Tablespace, where all this kerfuffle would be completely unnecessary?

>compress=n makes sense for uniform extent sizes ...

Compress=n makes sense in just about every situation I can imagine. There are NO good reasons why a segment should comprise just one extent.

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

Firt mistake. Index very, very seldom need rebuilding. The re-use of deleted leaf entries happens all the time making a rebuild an expensive operation, and an unnecessary one too. Possible exceptions are indexes on monotonically incrementing sequence numbers, where earlier entries cannot possibly be re-used.

> COALESCE
> /

Second mistake. If you've made the mistake of rebuilding an index, a coalesce will achieve precisely nothing. It is designed to scan along the base of an index, and merge adjacent partially-filled leaf nodes into single, fully packed leaf nodes. After a rebuild, there will be no partially filled leaf nodes. So the coalesce is yet more I/O to precisely no effect.

> ALTER TABLESPACE TS_IND COALESCE
> /
>

You don't ever need to coalesce locally managed tablespace.

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

Why you are curing fragmentation, and not preventing it in the first place, I guess I can't imagine.

Regards
HJR
> Jan
>
>
Received on Thu Sep 12 2002 - 03:33:55 CDT