2

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.

  1. The execution plans the general query, not forcing any index, shows that an Index oneIndex is used. No record is returned.
  2. Forcing another index, using the same query returns the missing record.
  3. Setting oneIndex to invisible and back to visible...
  4. 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 ???
  5. 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?

Improve this question
3
  • 2
    You should report this type of thing to Oracle support.
    – Mat
    Commented Jun 26, 2013 at 5:30
  • 1
    If what you are describing is correct, you encountered a bug. You'd want to contact Oracle Support for assistance dealing with bugs. It's entirely possible that there is a patch they can send you that would fix the issue. Or they can identify the bug and tell you exactly what causes it and the workarounds.
    – Justin Cave
    Commented Sep 8, 2015 at 5:50
  • 1
    Maybe this is caused by the index being corrupted. Does it work if you re-create the index (alter index ... rebuild). Also what is the definition of the index. Can you show us the complete statement that creates the index (edit your question, don't post code in coments)
    – user1822
    Commented Sep 8, 2015 at 6:05

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.