Re: Does NOT automatically cause a full table scan? 9.2.0.5

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Fri, 3 Nov 2006 15:28:44 GMT
Message-ID: <J85uzy.4no@igsrsparc2.er.usgs.gov>

Ben wrote:
> frank.van.bortel_at_gmail.com wrote:

>> On Nov 3, 2:37 pm, "Ben" <bal..._at_comcast.net> wrote:
>>> 9.2.0.5 Ent Ed AIX5L
>>>
>>> Does NOT automatically cause a full table scan or is that dependant
>>> upon histograms and the skew of the data? For example a statement like
>>>
>>> select t.a, t.b, t.c
>>> from t
>>> where t.b = 'abc'
>>> and not t.a = 'xyz';
>> Depends on the existance of indexes in the first place.
>> Not quite sure what you are actually asking. Did you
>> expect a FTS and you did not get one?

>
> yes, sorry, for example sake we'll say that t.a is indexed. I was just
> told that if a statement has NOT in it, then regardless of all else,
> you would get a full table scan. I don't think anything in Oracle is
> that cut and dry though.
>

In your case, you also have t.b equivalent to a distinct value. So if there is an index on t.b, Oracle may choose to use it (depending on the skew of the data, and assuming CBO). The "not t.a = ...." clause will suppress any index you have on t.a. You could rewrite this as "t.a <> ...", but it still will not use the index on t.a.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Fri Nov 03 2006 - 09:28:44 CST