RE: Very Strange Query Access Plan

Hi Tom,  

Yesterday, queries against this table used the index. Today, they do not. There was no large data load overnight. We add about 10,000 records per day. This behavior has happened before where the index gets ignored for a day and then used the next day.

I really need to use an index here. Queries via ssn are executed all day long and take 10 minutes to complete. CPU is pegged at 99%.

What am I missing? Maybe histograms? Haven't used them before, so any suggestions would be helpful.  

Maybe you would not need to use histograms.  

If you don't mind sending a 10053 trace excerpt for the query (maybe using a simple test query using ssn as the only predicate), showing the table & index statistics , and the cost assigned to each index, it would help to see what's going on. It appears a not very appealing index for the optimizer (625,000 out of 18 million and many nulled rows).  

In the extreme case you can also workaround this situation making this index's statistics more attractive to the optimizer, using dbms_stats.set_index_stats . But it's worthy take a look before at an 10053 trace , though.  

alvaro

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