Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
2  
You should report this type of thing to Oracle support. – Mat Jun 26 '13 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 Sep 8 '15 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) – a_horse_with_no_name Sep 8 '15 at 6:05

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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