Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!a26g2000pre.googlegroups.com!not-for-mail
From:  "fitzjarrell@cox.net" <fitzjarrell@cox.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: "Not in" subquery
Date: Wed, 06 Jun 2007 14:19:42 -0700
Organization: http://groups.google.com
Lines: 100
Message-ID: <1181164782.038066.241730@a26g2000pre.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>
   <1181163132.627402.270390@n15g2000prd.googlegroups.com>
   <1181163439.583077.229940@g37g2000prf.googlegroups.com>
   <1181163994.374479.270020@o11g2000prd.googlegroups.com>
NNTP-Posting-Host: 138.32.32.166
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1181164782 5270 127.0.0.1 (6 Jun 2007 21:19:42 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 6 Jun 2007 21:19:42 +0000 (UTC)
In-Reply-To: <1181163994.374479.270020@o11g2000prd.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: a26g2000pre.googlegroups.com; posting-host=138.32.32.166;
   posting-account=B_KC8Q0AAADcSTVy4DZ59utFaLrVLOo4
Xref: news.f.de.plusline.net comp.databases.oracle.server:199053

On Jun 6, 4:06 pm, Amritha.Da...@gmail.com wrote:
> On Jun 6, 4:57 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
>
>
> > On Jun 6, 3:52 pm, Amritha.Da...@gmail.com wrote:
>
> > > 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.- Hide quoted text -
>
> > > - Show quoted text -
>
> > The query plan is STILL important to discover what Oracle is currently
> > doing with this code and what, if anything, can be done to coerce it
> > to do something else.
>
> > Provide an explain plan for this statement, and the DDL for the table
> > and indexes.
>
> > David Fitzjarrell
>
> Hi David,
>
> Being a developer I can not have access to generate DDL.
> But As explained I will be executing this SQL through a stored
> procedure to update
> reason code for the qualified records in the sub query.
>
> Please advise.
>
> Thanks- Hide quoted text -
>
> - Show quoted text -

I have advised.  You haven't provided sufficient information to answer
your question.  When and if you do I or someone else can proceed.


David Fitzjarrell