Take the 2-minute tour ×
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.

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
1  
You should report this type of thing to Oracle support. –  Mat Jun 26 '13 at 5:30

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.