Re: Re: Any known problems using NOT IN ?

btw, a straight not in without a hash_aj, tends to get hideous bench marks.
>
> From: Mladen Gogala <mladen_at_wangtrading.com>
> Date: 2003/12/18 Thu AM 09:14:24 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: Re: Any known problems using NOT IN ?
>
> Actually, because relational database conform to the rules of set theory,
> I find it preferable to use "MINUS" wherever possible. Oracle optimizer
> is trained to spot set operations and they usually generate sort/merge
> or hash based execution plan, while "NOT IN" and "NOT EXIST" can generate
> NL plan, which is, generally speaking, undesired when you do set operations.
>
> On 12/18/2003 12:39:26 AM, Charu Joshi wrote:
> > Siddharth,
> >
> > The NOT IN query fails to return rows, if the inner sub-query returns NULL
> > values. It is always recommended to use the NOT EXISTS clause, unless you
> > are sure that the inner query will not return any NULLs.
> >
> > Regards,
> > Charu.
> >
> > -----Original Message-----
> > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> > Siddharth Haldankar
> > Sent: 18 December 2003 10:54
> > To: Multiple recipients of list ORACLE-L
> > Subject: Any known problems using NOT IN ?
> >
> > Hi Gurus,
> >
> > I have a problem using NOT IN clause in Oracle. However using NOT EXISTS,
> > gives me the right output. Are there any known limitations.
> >
> > This query selects from the master records wherein child records are not
> > active.
> >
> > select * from ct_software_release csr where
> >
> > csr.class = 'NS'
> >
> > AND csr.active_flag = 'Y'
> >
> > AND csr.os_id_pk not IN
> >
> > (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs
> >
> > WHERE crs.active_flag = 'Y');
> >
> > The sub-query in the above case gives 1800 rows. The above query fails to
> > give any rows.
> >
> > select * from ct_software_release csr where
> >
> > csr.class = 'NS'
> >
> > AND csr.active_flag = 'Y'
> >
> > AND NOT EXISTS
> >
> > (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs
> >
> > WHERE crs.os_id_fk1 = csr.os_id_pk
> >
> > AND crs.active_flag = 'Y');
> >
> > This above query works fine.
> >
> > Thanks
> >
> > ----------------------------------------------------------------------------
> > --
> >
> > Siddharth Haldankar
> >
> > Zensar Technologies Ltd.
> >
> > Cisco Systems Inc.
> >
> > (Offshore Development Center)
> >
> > # : 091 020 4128394
> >
> > shaldank_at_cisco.com
> >
> > s.haldankar_at_zensar.com
> >
> > *********************************************************
> > Disclaimer
> >
> > This message (including any attachments) contains
> > confidential information intended for a specific
> > individual and purpose, and is protected by law.
> > If you are not the intended recipient, you should
> > delete this message and are hereby notified that
> > any disclosure, copying, or distribution of this
> > message, or the taking of any action based on it,
> > is strictly prohibited.
> >
> > *********************************************************
> >
> > Visit us at http://www.mahindrabt.com
> >
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <ryan_oracle_at_cox.net
  INET: ryan_oracle_at_cox.net

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