I don't think using > vs. >= will make a difference, but the stats could prove me wrong.
Consider partitioning the table on the allowed status values. You can accomplish this using the ardb.cfg file for new tables or using the ardb.conf file plus your dba for existing tables; though this will require some offline time to convert existing tables, esp. those with large amounts of data (read worklog and audit trail diary fields). If you partition the table into 3 sections based on range (keyword "range partition"): PartitionA: 0-4 PartitionB: 5 PartitionC: 6 Then also include in the ardb.cfg to create the index on the status field using the same partition ranges. There is a way in 9i and higher to allow row relocation between partitions that you will have to investigate. The keywords escape me at the moment. See these links for more specifics: http://www.oracle.com/technology/products/oracle9i/datasheets/partitioning.html http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/partiti.htm One little gem I will throw out there is that the ardb.cfg file only interprets the first 314 or so characters of the clause you provide, so don't overstep that limit or the table/index creation will fail. This method will ensure your server will use the index provided for the status column. Axton Grams On 5/3/06, McKenzie, James J C-E LCMC HQISEC/L3 <[EMAIL PROTECTED]> wrote:
** 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 __20060125_______________________This posting was submitted with HTML in it___
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
