Re: Rebuilding and index

> Gentlemen:
>
> Here is my situation
>
> I have a badly fragmented INDEX object
>
> I am going to drop it
> I am going to recreate it using one single extent (storage INITIAL ????)
>
> My question is :
>
> How do I go about finding how big to make this first INITIAL extent ??

Well, ANALYZE the index first, and/or look in the data dictionary tables to find the number of blocks the index currently takes. If the index is badly fragmented, you'll have to take into account the empty space in the blocks. Obviously if you drop and recreate the index this will get rid of the wasted space, so you could then look at how much space the index is actually taking - and then drop and recreate it again, but this time with a correct initial extent.

> Are there any other implications, such as enough space in the TABLESPACE ??

Well, if you create the index back in the same tablespace you should be OK. However, remember the extents have to be formed from contiguous data blocks, so if you set a large initial extent and you haven't got enough contiguous free space the create will fail - so check DBA_FREE_SPACE first. Also note that you should not necessarily try to cram objects into just one extent, a few extents, correctly sized, can actually be a better solution (look at the Oracle White Papers on Space Management).

Steve Phelan. Received on Wed Mar 19 1997 - 00:00:00 CST