RE: Riddle me this.....
Date: Thu, 9 Apr 2009 16:36:55 -0400
Message-ID: <8833494F383585499CB855121711D26313919937C6_at_JBXEXVS02.fplu.fpl.com>
I found some left outer joins in the view syntax. Not surprising that this sort of thing has cropped up. all the developers here are in love with unions and left outer joins it seems like. lol
If this is the case, I'll bring to the table that they need to start querying the base tables to get this information instead.
Thanks, everyone, for helping me figure this thing out!
Jeremy
Consider the environment. Please don't print this e-mail unless you really need to.
-----Original Message-----
From: Stephane Faroult [mailto:sfaroult_at_roughsea.com]
Sent: Thursday, April 09, 2009 4:27 PM
To: SHEEHAN, JEREMY
Cc: Taylor, Chris David; jeremy.schneider_at_ardentperf.com; oracle-l_at_freelists.org
Subject: Re: Riddle me this.....
Bingo.
Not a left outer join in the view, and/or a union, by chance? That could bring back some nice nulls in the subquery.
SF
SHEEHAN, JEREMY wrote:
> It does appear that a view is in the mix.... I will see about getting some explain plans happening. In the mean time, I'll see if gathering schema stats run in the db to see if that helps at all.
>
> MFSDB _at_ pmidbd > SELECT OBJECT_TYPE,OBJECT_NAME
> 2 FROM USER_OBJECTS
> 3 WHERE OBJECT_NAME IN ('ISOLINK_PJM_STTL_ESTIM_W_ISO','PJM_NM_STTL_COMPONENT');
>
> OBJECT_TYPE OBJECT_NAME
> ------------------- --------------------------------------------------------------------
> VIEW ISOLINK_PJM_STTL_ESTIM_W_ISO
> TABLE PJM_NM_STTL_COMPONENT
>
> Jeremy Sheehan
> Oracle DBA
> Jeremy.Sheehan_at_fpl.com
> (561) 304-5769 - office
> (561) 625-7196 - fax
> Consider the environment. Please don't print this e-mail unless you really need to.
>
>
> -----Original Message-----
> From: Stephane Faroult [mailto:sfaroult_at_roughsea.com]
> Sent: Thursday, April 09, 2009 4:04 PM
> To: SHEEHAN, JEREMY
> Cc: Taylor, Chris David; jeremy.schneider_at_ardentperf.com; oracle-l_at_freelists.org
> Subject: Re: Riddle me this.....
>
> SHEEHAN, JEREMY wrote:
>
>> No rows returned....
>>
>> Jeremy
>> Consider the environment. Please don't print this e-mail unless you really need to.
>>
>>
>>
> Jeremy,
>
> No view in the mix? My initial thought was the same as Jared's (null
> values). The obvious difference between the two queries is that with the
> '=' you tell Oracle that you expect one value (as it doesn't trust you
> it will try anyway to get a second one to prove you wrong - but it knows
> it has at most two rows to return to either answer the question or
> fail). In the second case all bets are open, and it will have to base
> its estimate on stats and guesswork. In other words, all conditions are
> met to see different execution plans, and the different results actually
> prove you have different execution plans (no, I don't want to see them).
> Adding something like 'and rownum = 1' to the subquery with the in ()
> would probably bring you back to the same result as the '=' query.
> There must be some ugly query rewrite, and if the "table" in the
> subquery is actually a view, the rewrite may go pretty far and hit nulls.
>
> Stephane Faroult
>
>
>
>
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
i0zX+ n{+i^ Received on Thu Apr 09 2009 - 15:36:55 CDT