number of extents question

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 Received on Tue Sep 10 2002 - 14:40:23 CDT