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: Ed Prochak <edprochak_at_gmail.com>
Date: Wed, 06 Jun 2007 21:24:37 -0000
Message-ID: <1181165077.669445.252270@a26g2000pre.googlegroups.com>


On Jun 6, 4: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

NOT IN is slow in Oracle 8 and 9 (I think the optimizer wised up sometime in release 9). It must produce the entire resultset for the subquery for every row of the RTable table.

Convert it to a NOT EXISTS correlated subquery

                and NOT EXIST ( Select F_ID from RTable rt2
                                    Where rt2.FKey = 1234
                                    and rt2.SFKey = 1
                                    and TO_NUMBER(rt2.REL_CD) = 1
                                    and Reason Is Null
                             and rt2.F_ID=rt1.F_ID );

Without the explain plan, this is only a fish. To earn your fishing pole, study the explain plan of both the slow NOT IN query and teh faster NOT EXISTS query.

and BTW, your where conditions look strange. are you sure you need the

                                    and Reason Is Null
in the subquery?

  HTH
  ed Received on Wed Jun 06 2007 - 16:24:37 CDT

Original text of this message

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