Axton:
That was directly from the PT&T class and was enforced by some basic ORACLE training that I received.
Here is gist of the training:
If the query can be closed end (i.e. equals) then the optimizer will look to see if the index can be used. This happens at the point where about eight percent or less of the index entries are pulled back.
If the query is not closed end (i.e not equals) then the optimizer will do a full table scan.
So the key here is what is and is not closed end. If the entry has an equals sign in it like the following:
'Status' <= "Pending" the optimizer SHOULD look at the index and see approximately how many entries would be pulled back.
'Status' < "Resolved" SHOULD result in a full table scan.
The key here is that the data in the field must be highly variable OR the data is bunched around a single value. In this case, over 280,000 of about 300,000 have a single value with the remaining 20,000 (less than 8%) are scattered around the remaining values. So, if the training I received is correct, using the <= value should result in an index scan IF you do not use the value with 280,000 entries, which is the case here.
I don't have a form with 1,000 let alone 100,000 entries. Does anyone want to confirm/deny that this is the case?
James McKenzie
L-3 GSI
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Axton
Sent: Wednesday, May 03, 2006 4:54 PM
To: [email protected]
Subject: Re: Oracle 9.2.0.5 CBO Not Using Index
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
