problem of allocate of the extents

[dngaya]

hi ,
some time ago I have write a script which allows me to get the segments which have more than 1000 extents in a base oracle 7.3.4 on HP Unix. I have just noted that there are one indice which, each week, the number of extent increase and in more there is much block, here below the result:

name of the index: OAS_DOCLINE_IND1
blocks: 117400
semaine1: 1135
semaine2: 1165
semaine3: 2232
semaine4: 2286

When I execute the follow request:
SQL > select index_name, initial_extent, next_extent, min_extents, max_extents, pct_free from dba_indexes where index_name=' OAS_DOCLINE_IND1 ' and owner=' CODPRD';
I receive the following results:

index_name = OAS_DOCLINE_IND1
initial_extent=5529600
next_extent=102400
min_extents=1
max_extents=2147483645 (unlimited)
pct_free=10

I will like to know taking into account this evolution, is what there is not a disadvantage which will be able can be to have problems on the performances and how then-to make for reorganize this index.
thank you for your assistance.

[Jean-Luc Oudart]

Hi dngaya,

You can rebuild your index with different storage parameters.

alter index
storage ( initial 4096K next 4096K )

and any other parameter you like to change.

Rgds,
Jean-Luc
PS : if you rebuild in same tablespace ensure you have enough space.

[Hari Kumar]

Performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:
Alter index rebuild tablespace ;

Otherwise the index will be moved to the temporary tablespace, Care to be taken about the tablespace size too.

[Graham Cameron_1 · Accepted solution]

Your initial extent is 5M, but your next is only 100K, which may expalin why you are getting so many extents.
DBA practice here is to always have initial=next with pctincrease 0, and I would suggest and rebuild the index with these storage parameters.
Use ALTER INDEX ... REBUILD, else the existing fragmentation will still be present.
-- Graham

[Graham Cameron_1]

Sorry, didn't spot you were on oracle 7.
Not sure if ALTER INDEX REBUILD was available then.
You may have to drop and recreate it.
-- Graham

[Hari Kumar]

I feel happy if you give the feed back that
u have followed the suggestions or not?, if then what was the result, any suggestions from your side e.t.c

[Jean-Luc Oudart]

What is wrong with my solution ?

Rgds,
Jean-Luc

[Yogeeraj_1]

hi,
*
The answer provided by Jean-Luc is also a valid one.
*
Is it time that you migrate to 8i or 9i? and benefit from locally managed tablespaces?
*
regards
Yogeeraj