What happens when rebuild index failed?
614497Jul 9 2008 — edited Jul 9 2008Our db is 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
Server is Windows Server 2003 Version V5.2 Service Pack 2
In the rebuild index log we found one index failed to be rebuilt due to not enough free space in this index tablespace. We were waiting until 9PM to add more space to the tablespace and rebuild the failed index. Meanwhile we noticed the index is still in VALID status in dba_indexes but the database was extremely slow. My boss said it is because the failed index rebuild caused the database acted so slowly but I am kind of doubt about it. After the index was successfully rebuilt the db went back to normal performance. So I am kind of believing that failed index rebuilt does make huge difference. My old thought is when Oracle rebuild index it actually create a new index in the free space and when the new index is successfully created Oracle will use the new index and drop the old one. If rebuilt failed for some reason Oracle can still use the old index. Is this correct? If it is correct how can you explain the extreme slowness of the db? If not, what really happens when rebuild index fails?
Thanks a lot for your help,
Shirley