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.
> [EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
