In reading various information on the web there seems to be a great debate over rebuilding standard Oracle indexes( how often and how affective it is etc.). I have a table that experiences a large number of deletions plus others with not so heavy deletion rates. I am suspecting that performance may be enhanced if the index is rebuilt but I'd like to be able to determine this a head of time (some systems cannot use ONLINE option and need a maintenance window etc.).
1. How can I determine if an index needs to be rebuilt, i.e. is there a formula that shows efficiency etc.?
2. Can you recommend SQL that can be run to determine indexes that need to be rebuilt?