When and why oracle indexes become invalid?
database, indexing, oracle
Solution
Under normal operation indexes don't become invalid in Oracle.
And they rarely if ever need a "rebuild" either. Ssee this Ask Tom article for details. Jonathan Lewis' blog also contains very useful information regarding this topic:
The only possible ways I can think of that an index gets invalid is when it has been disabled manually (`alter index foo disable`) or when data was loaded through direct path inserts e.g. when using SQL*Loader with the `direct=true` option.
Edit:
Of course I forgot partitioning. And Winfried is right with his answer.
Problem
I always thought B-tree indexes are always updated (rebalanced) after each insert, update or delete. However it looks like Oracle does not update it after deletes (deleted leafs still remain in index tree). How about inserting and updating the rows where we use B-tree index type? Does index is updated during each insert/update? The other question is why and when indexes become invalid so they needed to be rebuilt? I'm mostly concerned about B-tree indexes, but also would be interesting to know when other types of indexes must be rebuild.