Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: "Not in" subquery

Re: "Not in" subquery

From: <Amritha.Datta_at_gmail.com>
Date: Wed, 06 Jun 2007 14:20:56 -0700
Message-ID: <1181164856.787014.322450@q19g2000prn.googlegroups.com>


On Jun 6, 5:19 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 6, 4:06 pm, Amritha.Da..._at_gmail.com wrote:
>
>
>
> > On Jun 6, 4:57 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Jun 6, 3:52 pm, Amritha.Da..._at_gmail.com wrote:
>
> > > > On Jun 6, 4:47 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > > On Jun 6, 3:43 pm, Amritha.Da..._at_gmail.com wrote:
>
> > > > > > On Jun 6, 4:31 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > > > > On Jun 6, 3:22 pm, Amritha.Da..._at_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

Thanks David! Received on Wed Jun 06 2007 - 16:20:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US