Oracle 7.3.2.3 not using Indexes

Okay, I've been having a wierd problem with Oracle 7.3.2.3 on Solaris 2.5 where it appears that Oracle just stops using certain indexes from time to time. This does not appear to have anything to do with Cost Based Optimizer as I can switch to Rule mode and the indexes still won't be used in queries when they should be. The tables are analyzed with compute statistics, and the indexes are usually rebuilt automatically after direct path loads.

I've checked the index creates and they are okay and the indexes are marked as valid. I've run analyze against the indexes and tables and they come out fine. As a last resort, I can DROP and rebuild the indexes and they are normally used correctly afterwards. This has happened several times to us, and I can find no reason for the indexes to just suddenly not be used anymore. We're running a data warehouse which is at about 45 GB currently. Some of the tables are around 50 to 60 million rows. I'm going to contact Oracle Support about this, but was curious if anyone else has experienced this. We've had a number of problems with the CBO on 7.3.2.3 and have 3 patches on the database to fix various problems. I'm just wondering if running a data warehouse on Oracle 7.3.2.3 has stretched it too far, as this seems to be a pretty buggy release from what I've seen so far.

Any ideas, or theories are welcomed!

-- 
Stephen A. (Tony) Rodgers
Prominence Consulting, Inc.

member Independent Computer Consultants Association
http://www.icca.org/firms/f15498.htm


---------------------------------------------------

tony_at_prominenceconsulting.com
Phone:	(404) 982-9955
Fax:	(404) 982-9001


---------------------------------------------------