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.
[EMAIL PROTECTED] 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@hotp To: Multiple recipients of list >ORACLE-L <[EMAIL PROTECTED]> > op.com> cc: > Sent by: Subject: RE: IN vs. EXISTS > [EMAIL PROTECTED] > 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: [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).
