Index causes poor performance in query that doesn’t use it

I’ve been working for hours on a performance issue the past week and this one and found a strange situation:  A new function based index sped up the queries that used it, but slowed down other queries that didn’t use it.  I recreated the problem on an 11.1.0.7 database and I couldn’t recreate it on an 11.2.0.3 database.

The function based index was on an index organized table.  The plan with or without the index looks the same:

------------------------------------------------------
| Id  | Operation          | Name                    |
------------------------------------------------------
|   0 | SELECT STATEMENT   |                         |
|   1 |  SORT ORDER BY     |                         |
|*  2 |   INDEX UNIQUE SCAN| RTD_DOCUMENT_PK         |
|*  3 |    INDEX RANGE SCAN| RTD_DOCUMENT_IX_DOCDATE |
------------------------------------------------------

But the predicates change on the last two steps of the plan.  Here are the predicates without the index:

   2 - access(...deleted for clarity...)
   3 - access(...deleted for clarity...)
       filter(TO_CHAR("CUSTOMERKEY") LIKE '90467978%')

With the function based index:

   2 - access(...deleted for clarity...)
       filter(TO_CHAR("CUSTOMERKEY") LIKE '90467978%')
   3 - access(...deleted for clarity...)

Without the function based index the plan uses the second column of the normal index and with the FB index it doesn’t.  Here is the normal index:

CREATE INDEX RTD_DOCUMENT_IX_DOCDATE 
ON RTD_DOCUMENT
(DOCUMENTDATE, CUSTOMERKEY);

So, for some reason the existence of the function based index affected whether CUSTOMERKEY was retrieved from the secondary index or from the primary key.  The plan that retrieves CUSTOMERKEY from the primary key accesses many more buffers.  In our real production scenario it accessed 4,000,000 buffers with the function based index in place and 22,000 without it even though the function based index wasn’t used in either case.

– Bobby

P.S. Here is the testcase.

P.P.S.  I found one bug that sounded similar but it is supposed to be fixed in 10.2:

Bug 4198156

Index-only retrieval not chosen with IOT and function based index [ID 4198156.8]