Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CONSISTANT GETS

Re: CONSISTANT GETS

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Fri, 15 Nov 2002 12:43:42 -0800
Message-ID: <F001.00504AEB.20021115124342@fatcity.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US