Hi Markus

First, here is my index:

CREATE UNIQUE INDEX myschema.myindex ON myschema.mytable (myobject, valid_to);

I get no record with the following query (explain plan: unique scan, uses the Index myindex):

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
   myobject = 'ABC.123';

But I know the record is there. So I try this (explain plan: full table scan):

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
   myobject = 'ABC.123';

And I suddenly get my record. So by either using or not using the index I get two different results. I think I know the fix for this: index rebuild. But what I don't get is how it is even possible to end up with such a corrupt index? And even more important: how can I prevent such a behavior in the future?

(DBMS is Oracle 11g EE 11.2.0.2.0 64bit)

PS: very nice book of yours by the way :)

Cheers