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: <fitzjarrell_at_cox.net>
Date: Wed, 06 Jun 2007 13:57:19 -0700
Message-ID: <1181163439.583077.229940@g37g2000prf.googlegroups.com>


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 Received on Wed Jun 06 2007 - 15:57:19 CDT

Original text of this message

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