Oracle-L: RE: IN vs. EXISTS

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Sat, 19 Oct 2002 08:48:24 -0800
Message-ID: <F001.004EE282.20021019084824@fatcity.com>

Rick,

It depends on your usage....

If you use exists for checking any valid value from database like a trigger in Oracle Forms for acceting valid values it just check the first value and comes back very fast (much much faster than IN).

But in case if you want to retrieve all applicable values/rows than IN is a better alternative....

Using IN in forms trigger(field level) for validation purpose, will never be a good idea and not performs better than exist....

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Sat, 19 Oct 2002 07:24:40 -0800

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:

INET: Rick_Cale_at_teamhealth.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
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: dcutrone

INET: dcutrone_at_hotpop.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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:

INET: Rick_Cale_at_teamhealth.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

Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mohammad Rafiq
INET: rafiq9857_at_hotmail.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 Sat Oct 19 2002 - 11:48:24 CDT