RE: IN vs. EXISTS

[Mohammad Rafiq]

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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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@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:
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: dcutrone
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).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).


_________________________________________________________________
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: [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).