This content has been marked as final. Show 3 replies
Generally speaking, none of your indexes will need rebuilding, so forget all about them and let them do their work without fiddling with them.
Right: there will always be a few exceptions to such general rules. There are no easy rules to follow, though, despite what some so-called experts will tell you.
Classically, you are supposed to do analyze index blah validate structure and then query the view INDEX_STATS. If DEL_LF_ROWS divided by LF_ROWS is greater than about 0.25 (that is, 25% of the index is marked as deletable but hasn't been lately), and if that ratio doesn't improve by itself over the course of the next few days, then the index might be considered a candidate for a rebuild.
Rather less classically, but rather more practicably, if you have metrics for how queries are supposed to perform and you regularly track against those metrics, then when the metric indicates a slow-down, you might at least investigate whether the index has blown out and could do with a bit of re-compaction.
You might also reasonably rebuild your index if moving it into archive partitions or soon-to-be read-only tablespace, for then you will want the index to be as compact as possible.
You might also very reasonably drop indexes before a bulk load and therefore equally reasonably re-create them after the load.
Similarly, if you discover you got the order of columns wrong (because your queries always want to select things by column S and D, but your index is built on columns D, G and S), then you will be looking to drop and re-create the index from scratch.
But most indexes really don't need routine maintenance.
Reorganisation of tables may help rather than Rebuilding