This is an extract from another forum "Ask Tom", so thanks Tom.

-------------------------------------------------------------
So, what do you think was happening here? Well, the fact is that indexes, like
people, have a certain "weight" they like to be at. Some of us are chubby --
some skinny -- some tall -- some short. Sure, we can go on a diet -- but we
tend to gravitate BACK to the weight we were. The same is true for indexes --
what happened to this person is their index wanted to be wide and fat and EVERY
MONTH they rebuilt it (put it on a diet). It would spend the first half of the
month then getting fat again and generating gobs of redo due to the block splits
it was undergoing to get there.

In this case, rebuilding the index on their system had these effects:

o the system would generate 4.5 times the redo
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load

until the system got back to where the system actually wanted to be. And then
-- AND THEN -- they (the dba's) would do it all over again!!!! They would
destroy the equilibrium that the system worked so hard to get to. Bravo!!


(can you tell what my opinion is on regularly scheduled index rebuilds??? I
despise them).


If you are suffering from fragmentation -- your problem is not the index
rebuilds. It would be because you are using DICTIONARY managed tablespaces.
Switch over to locally managed tablespaces and you'll never have fragmentation
again. So, my suggestion -- one last index rebuild:

o create a locally managed tablespace
o alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );

and then forget about them. You will rebuild an index in response to IDENTIFIED
and DEGRADED performance -- period. You will not rebulid indexes based on a
schedule. Period.

It is the RARE index that needs to be rebuilt.
It is not a rule that indexes need to be rebuilt.

--------------------------------------------