Oracle index behaviour

Question

When I query my table for a specific object like this, I'll get an empty result set:

SELECT 
   a, b, c, valid_to, pk_mykey, myobject
FROM
   myschema.mytable
WHERE
   valid_to = to_date('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
AND
   pk_mykey > 0
AND
   myobject = 'ABC.123';

But I know the record has to be in the table!

So I use this query and it will suddenly show up:

SELECT 
   /*+ NO_INDEX(mytable myindex) */
   a, b, c, valid_to, pk_mykey, myobject
FROM
   myschema.mytable
WHERE
   valid_to = to_date('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
AND
   pk_mykey > 0
AND
   myobject = 'ABC.123';

So by using the index the records won't show, but without index it will? How is this possible? What am I missing here? What is wrong with my table and/or my index? Any idea how I can solve this?

(DBMS is Oracle 11g EE 11.2.0.2.0 64bit)

Answer 1

Theoretically the content of your index can be "invalid" due to some Oracle bug. This could happen on some early RAC releases. Try to execute the statement

ALTER TABLE myschema.mytable VALIDATE STRUCTURE CASCADE;

This will "crosscheck" table content against it's indexes. Or try to rebuild the index.

Answer 2

Have you confirmed that there is a difference in the execution plan?

If you are getting different results with and without the index then I'd consider dropping the index and recreating it. Do not just rebuild the index, as the new segment will be based on the data in the old, and if you do have a corruption problem that will not fix it.