Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible We are using Oracle 11g, 11.2.0.3.

We know a record exists in a table but a select is not returning it for some odd reason.


The execution plans the general query, not forcing any index, shows that an Index oneIndex is used. No record is returned.
Forcing another index, using the same query returns the missing record.
Setting oneIndex to invisible and back to visible...
Rexecuting the initial query (not forcing any index) does return the record (but somehow, the execution plan is not using oneIndex anymore). Maybe it woke up and found that stats were old ???
Taking the first query again and forcing the use of the faulty index (oneIndex) works fine now.


Facts: Statistics were quite old.

Session Parameter for ORACLE SQL Developer:

ALTER SESSION SET 
   OPTIMIZER_FEATURES_ENABLE = '11.2.0.1' 
   OPTIMIZER_MODE = FIRST_ROWS_1 
   "_HASH_JOIN_ENABLED" = FALSE 
   "_PUSH_JOIN_PREDICATE" = TRUE 
   "_PUSH_JOIN_UNION_VIEW" = TRUE 
   "_COMPLEX_VIEW_MERGING" = TRUE 
   "_TABLE_SCAN_COST_PLUS_ONE" = TRUE 
   "_ORDERED_NESTED_LOOP" = TRUE 
   "_NEW_INITIAL_JOIN_ORDERS" = TRUE 
   "_UNNEST_SUBQUERY" = TRUE 
   "_INDEX_JOIN_ENABLED" = TRUE 
   "_LIKE_WITH_BIND_AS_EQUALITY" = TRUE 
   NLS_SORT = BINARY 
   NLS_COMP = BINARY 
   NLS_NUMERIC_CHARACTERS = '.,' 
   QUERY_REWRITE_ENABLED = FORCE 
   CURSOR_SHARING = EXACT 
   DB_FILE_MULTIBLOCK_READ_COUNT = 0
   OPTIMIZER_INDEX_COST_ADJ = 5
   OPTIMIZER_INDEX_CACHING = 95


The SQL is a bit had to follow since it is produced by a Content Management application. I will not provide it for now.


Q1: Why is an index no longer chosen by the execution plan after
switching to invisible and then back to visible?
Q2: How can Oracle momentarily not see a record when searched from an
index instead of another index?
The data returned should be always the same independent of the access paths used. It never depends. The result set is defined by the text of the sql statement. Hints are comments and do not influence what data is retrieved. Indexes are auxiliary data structures that facilitate data retrieval but have no influence on what data is returned.
So this is a bug and you should contact Oracle support.