Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: "Not in" subquery
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 Nullin the subquery?
HTH
ed
Received on Wed Jun 06 2007 - 16:24:37 CDT