Wrong results using Oracle Text
When querying a table using Oracle Text contains(col1,'some-value') > 0, we are getting false positive hits. Upon investigation into this phenomena we have found the following.
a token was indexed for a docid (ex: token ABCDEF ; docid 123456789)
This token pointed to multiple rowids in the table
At some point, one of the original entries in the indexed table was deleted.
Due to issues beyond our control, the OPTIMIZE statement did not get executed for this token to remove the deleted rowid reference.
Much more recently, another record was stored at the same FILE_ID/BLOCK#/ROW (rowid)
When querying this token, we get back the "new" record - which does NOT contain this token