Strange Index Behavior

During a performance problem I had with a query I wanted to create an index on the table. Since this was a large table I wanted the index to include all the needed columns so I won’t need to access the table at all. The query also used GROUP BY and MAX, so I thought a descending index would be best, as it will easier for Oracle to find the highest value (as it will be the first one).

Take a look at this test case:

create table gby (f1 number not null, d1 date not null);
insert into gby
select mod(rownum,1000), sysdate-dbms_random.value(1,100)
from dual
connect by level  select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0va95f97pf4d0, child number 0
-------------------------------------
select /*+ index(gby,gby_idx) */ f1,max(d1) from gby group by f1
Plan hash value: 4102401484
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |       |       |   337 (100)|          |
|   1 |  SORT GROUP BY NOSORT|         |   118K|  2538K|   337   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | GBY_IDX |   118K|  2538K|   337   (1)| 00:00:01 |
--------------------------------------------------------------------------------

That looks fine, what about the other index?

SQL> select /*+ index(gby,gby_idx_desc) */ f1,max(d1) from gby group by f1;
        F1 MAX(D1)
---------- ---------
         0 26-MAR-19
         1 23-MAR-19
...
1000 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9q4vud4act8fg, child number 0
-------------------------------------
select /*+ index(gby,gby_idx_desc) */ f1,max(d1) from gby group by f1
Plan hash value: 1700014820
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |   100K(100)|          |
|   1 |  SORT GROUP BY NOSORT        |              |   118K|  2538K|   100K  (1)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID| GBY          |   118K|  2538K|   100K  (1)| 00:00:04 |
|   3 |    INDEX FULL SCAN           | GBY_IDX_DESC |   118K|       |   337   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

As you can see, there is a table access where there shouldn’t be. Oracle should be able to have all the information it needs from the index.
It is reproduceable in 12.1 (mine has PSU 180417 installed), but not in 12.2 or 18c. So t
his is clearly a bug even though I couldn’t find the specific one in MOS.

Update

After realizing that it doesn’t reproduce for some people, I checked my environment and I forgot, but in this specific environment I use optimizer_features_enable=11.2.0.2 and it seems to be a bug in this version. Once I changed it to 12.1.0.2 it worked (with 11.2.0.4 it still doesn’t).
Interesting how when you set OFE, Oracle preserves the optimizer bugs of the old version as well…