| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: CONSISTANT GETS
Whittle Jerome Contr NCI wrote:
>
> Jared,
>
> I'm still on 7.3.4 but I'm sure that you are right about the WHERE
> clause in this case. It went from an INDEX FULL SCAN to an INDEX
> UNIQUE SCAN on the same index once the blasted concatenations were
> removed. One programmer says he likes to write it that way because
> it's simpler and nicer looking!
>
> Still I've seen some nice speed gains when just converting some WHERE
> statements from NOT IN to NOT EXISTS.
>
> YMMV
>
> Jerry Whittle
> ACIFICS DBA
> NCI Information Systems Inc.
> jerome.whittle_at_scott.af.mil
> 618-622-4145
Jerry,
The efficiency of NOT IN vs NOT EXISTS depends on the other criteria. I guess that in your case, where there is no other criteria, the table where you were fetching was not enormous. Basically this is an existence test. If you have a very selective criterion besides and the existence test is just an additional check, NOT EXISTS is what should be used. However, if this is the only criterion, the query is correlated - it means that it must be evaluated for each row. On a very large table it may be extremely costly. I don't remember if HASH_AJ and MERGE_AJ are available hints with 7.3. If they are, just try to rewrite the query
where (a, b, c, d) not in (select /*+ HASH_AJ */ e, f, g, h
from ...)
and then try again with MERGE_AJ (with set autotrace turned on). Check
the number of logical reads, you may be surprised (or maybe not :-)).
The hint is required with 8.x, but Oracle9 is smarter - hence Jared's
remark. Personally, I am pretty fond of an outer join with and
additional IS NULL condition when I can, it follows more or less the
same plan without any need for an hint.
-- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Nov 15 2002 - 14:43:42 CST
 |
 |