Why does Oracle optimizer choose a bad index?

    SELECT /*+ index(a MY_INDEX_N1) */ COUNT(*) FROM MY_TABLE a WHERE a.VARCHAR_COLUMN_1 = :B2 AND a.NUMBER_COLUMN_2 = :B1

The NUMBER_COLUMN_2 is NOT NULL in the table.

The index in the hint "MY_INDEX_N1" doesn't exist. (Looks like it was dropped by the application vendor in a patch and no one noticed). And yes, we know we shouldn't use hints like this. No one realized the developer did this until a recent problem. We'll fix that... it's not my question though.

Anyway, there are two other indexes on the table:

MY_INDEX_N17 ( NUMBER_COLUMN_2, VARCHAR_COLUMN1, SOME_OTHER_NUMBER_COLUMN98 ) MY_INDEX_N10 ( NUMBER_COLUMN_2, OTHER_VARCHAR_COLUMN99 ) This query recently became a performance problem and what I think happened is this:

• The query was submitted with bind variable :B1 NULL. (I know this part for sure)
• Oracle thought "oh, the condition NUMBER_COLUMN_2 = NULL will never be true, it does not matter what index I choose. I will choose MY_INDEX_N10 because it is smaller".
• It blows through the first 10-15 calls to this query where :B1 is null and then get into the 5-10 _thousand_ records to process where it is not null. Now, suddenly, that access plan is a dog.

So, questions:

1. Why wouldn't Oracle choose the slightly bigger MY_INDEX_N17 to hedge it's bet, since that index's 2nd leading column is also used in the query?
2. Why wouldn't Oracle (quickly? Eventually?) realize that this query is bind sensitive and reparse it? I see in AWR that it ran with this bad plan 68 times over the course of about 16 hours).

Thanks in advance of any insights from the experts who dwell here!

Matt

--
http://www.freelists.org/webpage/oracle-l