Monday, October 1, 2012

Will reclaim space from tablespace by rebuilding the index in oracle database?

Someone asked that,
"We want to reclaim space, so we will rebuild all of the indexes that have too much unused space. "

Answer is (from Tom kytes quote) :-
You can’t expect it, The index is right back the way it was. Because the system got back to where the system actually wanted to be. So indexes just grow all over again .You would just be wasting your time to rebuild it again causing this vicious cycle to repeat itself.

By the by, rebuilding the index on the system might have these effects:

o the system would generate 4.5 times the redo ( for some times/days)
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

Tom kytes also says, “this is (reclaim space) the funniest reason to rebuild index, since they just get fat anyway -- all but the degenerate cases stop growing. if you put them on a diet by rebuilding, they just get fat again anyway”


See here more about index rebuild
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112
https://forums.oracle.com/forums/thread.jspa?messageID=2017174&

No comments: