Jon:
Do you have an index on the field and is the field set to equals or leading in the QBE?
I forgot those two. If the QBE setting is ANY, then the query sent to Oracle will automatically have the Wildcard (%) character added to the front and end of the query item. This definitely changes the query. On selection items this should not matter.
James McKenzie
L-3 GSI
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Jon Chau
Sent: Thursday, May 04, 2006 7:06 AM
To: [email protected]
Subject: Re: Oracle 9.2.0.5 CBO Not Using Index
I also saw that in the PT&T training manual that I have, but it didn't make a difference when I tried. Thanks for the suggestion Axton, I will let you know how it goes.
On 5/4/06, McKenzie, James J C-E LCMC HQISEC/L3 <[EMAIL PROTECTED]> wrote:
> **
>
> 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
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
