Jon:
Your problem is using the < sign. This forces Oracle to check EACH and EVERY entry in the table. Try this:
'Status' <= "Pending"
This should use the index as you are now looking for a value that has an end.
James McKenzie
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Jon Chau
Sent: Wednesday, May 03, 2006 2:30 PM
To: [email protected]
Subject: Oracle 9.2.0.5 CBO Not Using Index
Hello Listers,
I've read some past threads on Oracle CBO optimization and database
tuning and have been having a constant battle with my DBA on how to
resolve the issue we are having. We have a HelpDesk form with an
index against the Status field and it is not being used when we are
using a range query. Example - 'Status' < "Resolved". Our
optimization mode is set to choose and due to the distribution it
wants/does a full table scan and causes several queries against the
Status field to timeout:
COUNT(C7) C7
---------- ----------
3 0 - New
597 1 - Assigned
192 2 - Work In Progress
133 3 - Pending
2083 4 - Resolved
283007 5 - Closed
34 6 - Recovered
As you can see there are under 300k rows on this form/table.
I looked into changing optimizer_index_cost_adj parameter so maybe the
CBO would use the index but it didn't have an effect. We tried
changing it to 10 on an session basis and ran our query. We checked
to see the suggested parameter value from a script that was given
here: http://www.dba-oracle.com/oracle_tips_cost_adj.htm and that
wanted us to use 0, which is almost like using rule based optimization
mode (my DBA said). Rule based does hit the index, but we want to
stay on choose. We also update statistics on a weekly basis. Does
anyone have any suggestions? The only thing I can think of is
archiving, but it sounds like there are higher volume systems out
there that don't have this problem.
Environment
Oracle 9.2.0.5 on HP-UX instance shared with another application
ARS 6.3 p13 Windows 2003
HelpDesk 6.0
TIA,
Jon
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
