Recently, we encounter a problem where a select query that has been working for many years in production environment stopped returning expected results. In further analysis of showed that data is there but the query did not return. After bit of hit and trial with the query, it returned the result; however, it required change in where clause.

The original query did not work and we have a query that returns the correct results. The question is what went wrong in db?

Since this is a query problem, the question is where did the where clause go wrong? Is this unknown oracle bug?

After involving dba adminstrator to investigate, we found that the index was corrupt. Ok, we have something. But key question is that why certain where clause is failing and not all. The answer to this question depends on how oracle optimize query. In this case, data was there but certain where clause did not return the data.

Here what we observed. When your query that is bot select and where clause can totally be answered by the index, then oracle db just looks up the index. Thus, it does not go to the table. Thus, if your index is corrupt, you are going to have a very bad day. However, if your query both select and where clause cannot entirely answered by index, then oracle does not depend on the index alone. Also, in this case, the oracle seemed to do the correct thing that is look up the table even the index does not return the data.