I have this query:
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
