index question

[dorothy]

If index segments in an index tablespace has extents of more than 20, what will happen to the
tablespace?
I know in tables, the data tablespace will become fragmented.
Will that happen to the index tablespace as well?

[sreddy]

Fragmentation holds good for both data and index tablespaces if extents are uneven, Index tablespace is not an exception.

[Edited by sreddy on 02-08-2001 at 06:47 PM]

[dorothy]

Assuming that I want to make all the extents one and set the next extent_accordingly. What do you think is the best way to resize these segements? Do you think I should drop them and recreate them? Or just resize it.

[sysdba]

alter index rebuild
should do it

[dorothy]

Are you saying that ALTER INDEX INDEX_NAME REBUILD will make all the extents one for the initial extent?

You mean if I have an index called pk_emp and I have set the initial and next extent be 1meg. And so far the index segment (pk_emp)has used 30 extents (making it 30m).
Are you saying that by me issuing the command
ALTER INDEX PK_EMP REBUILD will now make the initial extent for the same index 30meg?

[sreddy]

REBUILD option will rebuilds the index thus eliminating the current level defragmentation and its not going to stop defragmentation in future. You have to REORGANISE the tablespace using SAFE methodology. Find a tech paper on SAFE searching January postings. That talks about how to take care of fragmentation issues if you are so concerned about fragmentation of tablespaces.



[url]http://www.dbasupport.com/forums/showthread.php?threadid=6063[/url]

[Edited by sreddy on 02-08-2001 at 11:10 PM]