If the corruption is in the index, then rebuilding the index won’t help, because it will rebuild from itself with the corrupt information. If that is the case you will need to drop the index and create it again.
If re-creating the index doesn’t solve the problem, then the corruption may be in the table.
If the number of different values in the index is small, you could issue a SELECT statement against the table to get the indexed column, forcing a full table scan.
For example, if the indexed column is YEAR. The statement should be something like this:
<pre>SELECT /*+ FULL */ year from table_X where year not in (2000,2001,2002,2003,2004,2005,2006,2007,2008);</pre>
Being 2000-2008 the only values that exists in the index.
If this select returns some rows, those are the corrupted ones, and you will need to drop any index containing the column, delete or update the table with the appropriate values for this rows, and then create the necessary indexes again.
As for the possible causes, it could be a number of things:
- memory problems
- system crashes
- disk errors
- it could also be caused by disk repair utilities
Please tell us if you solve the problem.