Re: Oracle don't want to use indexes

Joseph wrote:
> Hi Folks!
>
> We have an oralce 9i that from 2 weeks seems to have troubles in
> performace. Seems that oracle don't want use indexes for 2 of our
> largest tables and many sql queries access in full table scan. what's
> happening?
>
> MAny Thanks
> BR

A number of possibilities but the one that immediately comes to mind is that based on information available to the optimizer it now no longer views the indexes as the optimal path.

So the question is: What has changed?

1. Statistics run?
2. Statistics not run?
3. Parameters changed?
4. Indexes dropped?
5. Inserts or updates changed the cardinality?
6. Change to optimizer environment invalidated an optimal plan?

Run an explain plan using dbms_xplan on your statement first as is and a second time after hinting and forcing index usage.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org