Re: number of extents question

Thank You, Howard, for Your comments. Let me respond to some of them.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> schrieb im Newsbeitrag news:3d805179_at_dnews.tpgi.com.au...
>
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> news:alpeuc$1rfm94$1_at_ID-152732.news.dfncis.de...
> > Hi, Yong Huang,
> >

 ...
> >
> > For Tables, we export them, make a COALESCE of the TS
> > and import them again.
>
> Gawd! What a lot of work.

Indeed. To be precise, I personally never did - the former specialists from whom we inerited the db did.
But they are gone.

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

LMT came with 8i, and we have just upgraded 2 months ago. And yet we did not change the DMTs. It´s time, though ...

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

OK, I understand now, reading the postings. But why then, many books point out that a segment _should_ be put into _one_ extent ? .
Why do still many dba courses teach this ? Are they just old fashoined, or did something change inside of the RDBMS, so that it once might have had a meaning ?

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

I am also thankful to any critics like this. I admit, I do not have much experience
in reorganizing.

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

Thank You for this information. Sereval books that I have been through did not mention it that precise.

> > ALTER TABLESPACE TS_IND COALESCE
> > /
> >
>
> You don't ever need to coalesce locally managed tablespace.
We still do not have LMT, see more below.

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

Pity, I came too late for the first place - because I would have done it. The db has been created 3 years ago.
And unless I cannot convince the management (for reasons taht I won´t reveal here),
I may not be allowed to make
"too big" changes. I like the concept of LMT just as well, unfortunately, it is
not well known around me. And anything new is "suspicious", You know ...

Though I do some admin works on it, I do not "own" the db. The management wants to keep control over any changes, so I am somewhat limited what to to - yet ...

Jan=) Received on Thu Sep 12 2002 - 06:28:43 CDT