as INDEX_STATS only show the details for the last validate structure issued you'll be wanting to script/PLSQL something to look at all your indexes. It's been done a thousand times before so there are plenty freely available. I'm sure this can be done 'online' but I'm scared to try it can someone confirm this for me?
If you are going to rebuild indexes then benchmark your performance before and afterwards. Probably you will find that there is no difference, but you might find that performance degrades. You might then reconsider whether you want to bother.
Hey, I'm not saying it can't happen, just that it's unlikely.
Benchmarking is the key -- if it's faster afterwards then keep on going, but don't go thinking that index rebuilds are a quick and easy step to happiness. If you're not benchmarking your improvement then you're wasting time -- rebuilding indexes every month turns into a little religious rite that must be performed to keep the database gods happy.
If your index contains a columns with a growing ID and older rows are deleted due to the apllication-design, then you can rebuild the index regularly because your index growth in space and your leaf-blocks are not filled enough for effective read.
Be aware of that index-rebuild can cause the optimizer to choose another execution-plan, after collecting stats. This is very problematic with partitioned indexes.
Instead of REBUILDING, consider COALESCE.
The command is :
ALTER INDEX indexname COALESCE;
By coalescing, Oracle merges near emplty blocks and frees them for future insertion. Moreover, there won't be lock on the table, so the availability of the table is ensured. In 24x7 env, this is a most command I have come across.