We are using Oracle 11g, 220.127.116.11.
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
oneIndexis used. No record is returned.
- Forcing another index, using the same query returns the missing record.
oneIndexto 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
oneIndexanymore). 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 = '18.104.22.168' 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?