Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!feeder1-2.proxad.net!64.233.178.134.MISMATCH!postnews.google.com!n15g2000prd.googlegroups.com!not-for-mail
From:  Amritha.Datta@gmail.com
Newsgroups: comp.databases.oracle.server
Subject: Re: "Not in" subquery
Date: Wed, 06 Jun 2007 13:52:12 -0700
Organization: http://groups.google.com
Lines: 63
Message-ID: <1181163132.627402.270390@n15g2000prd.googlegroups.com>
References: <1181161334.045765.206080@z28g2000prd.googlegroups.com>
   <1181161879.799618.189300@g37g2000prf.googlegroups.com>
   <1181162608.355821.208610@o11g2000prd.googlegroups.com>
   <1181162869.947308.234880@d30g2000prg.googlegroups.com>
NNTP-Posting-Host: 12.180.111.254
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1181163133 618 127.0.0.1 (6 Jun 2007 20:52:13 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 6 Jun 2007 20:52:13 +0000 (UTC)
In-Reply-To: <1181162869.947308.234880@d30g2000prg.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: n15g2000prd.googlegroups.com; posting-host=12.180.111.254;
   posting-account=6Swc_g0AAAAHBdgsqoSgMrgOVOv_Q0HR
Xref: news.f.de.plusline.net comp.databases.oracle.server:199048

On Jun 6, 4:47 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Jun 6, 3:43 pm, Amritha.Da...@gmail.com wrote:
>
>
>
> > On Jun 6, 4:31 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
> > > On Jun 6, 3:22 pm, Amritha.Da...@gmail.com wrote:
>
> > > > Hello,
>
> > > > Is there any alternative query to the below one?
>
> > > > update RTable set Reason = "XYZ"
> > > >             Where FKey = 1234
> > > >                 and SFKey = 1
> > > >                 and Reason Is Null
> > > >                 and F_ID  not in ( Select F_ID from RTable
> > > >                                     Where FKey = 1234
> > > >                                     and SFKey = 1
> > > >                                     and TO_NUMBER(REL_CD) = 1
> > > >                                     and Reason Is Null);
>
> > > > "not in" in the above query is taking alot of time and it is timing
> > > > out.
>
> > > > Please Advise.
>
> > > > Thanks
>
> > > I can only presume there is an index on (FKey,SFKey)?  Also, why are
> > > you using full quotes around your replacement text ("XYZ")?  Those
> > > should be single-quotes ('XYZ').
>
> > > Of course we can't really say much without an Oracle release (to four
> > > numbers) and an explain plan...
>
> > > David Fitzjarrell
>
> > Sorry It should be 'XYZ' I am using oracle 9i.
>
> > Thanks- Hide quoted text -
>
> > - Show quoted text -
>
> 'Oracle 9i' doesn't tell us much as that spans releases from 9.0.1.x
> through 9.2.0.x, with functionality differences between them.  The
> request was for:
>
> * Oracle release to four numbers : 9.x.y.z
> * An explain plan for your statement
>
> Please provide both.
>
> David Fitzjarrell

It is Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
I will be executing this SQL through a stored procedure to update
reason code for the qualified records.


Thanks.