Re: INDEX REBUILD

Hi:

I agree with everyone else's comments. I'd like to add that rebuilding indexes is a good idea. When rows get deleted from a table, the space gets reused. When rows are *deleted* from an index, the space IS NOT reused. Perform an ANALYZE INDEX indexname VALIDATE STRUCTURE, then look in the INDEX_STATISTICS table. The deleted_rows/total_rows will tell you what percentage of space is wasted. If half of the index block has deleted rows, for instance, then you have half as much real data in the buffer cache after an index read.

Please note, though, that putting the whole index in a single extent has absolutely no effect on performance. It is the same number of rows whether it is in 1 extent or 100 extents, and therefore take the same amount of time to read. Rebuilding (regardless of the number of extents) does make the index more dense. Check the tuning and administration guides on this note. The same is true for tables (ie. read performance is mostly independent of the number of extents).

Regards,
Dave

--
David C. Sisk
http://www.ipass.net/~davesisk/ORACLEonNT.htm


Paul Bennett wrote in message <364B3950.76136BE6_at_cc.gatech.edu>...


>I have issued the command alter index <index name> rebuild on a few

>indexes who had a lot of extents (in order to put the entire index in

>one extent and make it faster), however, when i run this command the

>number of extents does fall to one, but the size of the extent is just

>whatever the initial extent size was in the first place.  i would except

>this number to get bigger.  if the index used to have 200 extents in

>addition to the initial extent how can the entire index fit into the

>initial extent now that I rebuilt it.  Every index that I have rebuilt

>has done this.  is it just a coincidence?  is it because rows were added

>to make the other extents necessary and then some rows where deleted and

>therefore there are holes in the index?

>

>am I doing the right thing to the indexes?  did i misinterpret what the

>rebuild command does?

>

>thanks.

>