Re: number of extents question

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<alli1a$1ppeaj$1_at_ID-152732.news.dfncis.de>...
> Does it or does it not
> matter (that is: decreasing my performance)
> having 1, 10 or 200 extents for one index segment (and BTW, same question
> for tables) ?
>
> I have read some books and posts, but I find two opposite opinions:
>
> 1. It does matter, so I should set INITIAL to how big it is now and set NEXT
> to what I expect how it may grow,
> and I should reorganize whenever a segment gets beyond 10 extents.
>
> 2. It does not matter at all, just keep segments same sized (e.g. 1M) to
> avoid fragmentation.
>
> I am asking this because right now I am reorganizing some 100 indexes, all
> of different initial and next size,
> and half on my way now
> I see that the maximum allocable extent of the index TS has *decreased* from
> 8M to 6M now.
> Even coalesce after every rebuild does not have much effect.
>
> So I am asking myself if it would not be better to reorganize indexes with
> initials that would *not* start with
> 80M, but to have initial and next of about 10M ...
>
> My system is:
> Oracle EE 8.1.7 on AIX 4.3.3
>
> Any comments and tips from are helpful.
> ThanX in advance,
>
> Jan

Jan,

The number of extents does not affect the performance of queries, but it affects the performance of extents allocation/deallocation. Operations like "truncate table" takes longer with large number of extents because Oracle must free up more extents. I think that's why you are hearing two different arguments.

Whether or when to rebuild segements depends on your performance requirements on these type of operations. There should not be a fixed number of extents at which segments must be rebuilt, unless you are approaching max_extents.

I hope this makes sense to you.
Tony Received on Wed Sep 11 2002 - 16:02:51 CDT