Very Strange Query Access Plan
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Oct 2007 11:12:02 -0400
All,
Running on 9.2.0.7 on Aix.
I have a database table with 18 million rows in it. There is an SSN
column in the table. Only 625,000 rows have an SSN populated. I also
have an index on this column. Statistics on both the table and index
are gathered nightly (using dbms_stats with the estimated and default
sample options).
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.
Thanks
Tom
Other related posts:
