Re: IN vs. EXISTS

If I may add something it may be worth trying /*+ USE_HASH */ and /*+ USE_MERGE */after the select in the INNER query. Depends on a lot of factors but it may be very efficient, especially if the inner query returns many rows.

Rick_Cale_at_teamhealth.com wrote:
>
> Hi Diego,
>
> After performing quite a few test you are 100% correct. I appreciate your
> insight.
> It is conclusive using "IN" is the best approach.
>
> Rick
>
>
> dcutrone
> <dcutrone_at_hotp To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> op.com> cc:
> Sent by: Subject: RE: IN vs. EXISTS
> root_at_fatcity.c
> om
>
>
> 10/18/02 06:43
> PM
> Please respond
> to ORACLE-L
>
>
>
> Hello Rick,
>
> I think that if you use EXISTS instead of IN
> the optimizer will have to do a FTS on the big table
> because he can't use any avaiable index on it.
> And it's a big table....
>
> While if you use IN and you have an index in the
> parent table (the BIG one) this index CAN be used (here,
> field1 must be indexed),and I think it'll be much faster.
> Of course it depends on the index's selectivity as well.
>
> Also, remember that with the IN operator the subquery
> is executed just once, and with EXISTS it's executed
> once by each parent row (so it must execute very efficiently)
> And that IN can use parent indexes (when avaiable and some conditions
> are met) and can't use any indexes to resolve the subquery, while
> EXISTS can't use the parent query indexes and CAN use indexes on the
> subquery.
>
> Please correct me if I'm wrong.
>
> HTH
> Greetings
> Diego Cutrone
>
> Hi All,
>
> I have 2 tables
>
> BIG - 100 million records
> SMALL - 1 million records.
>
> I want to delete all the records in BIG that are in small.
> There is a PK on field1.
> Which of the below methods would you choose and why?
>
> DELETE FROM big
> WHERE field1 IN (SELECT field1 FROM small);
>
> DELETE FROM big a
> WHERE EXISTS (SELECT 1 FROM small B
> WHERE b.field1 = a.field1);
>
> Thanks
> Rick
>

-- 
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).