Re: Riddle me this.....

    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>

--
http://www.freelists.org/webpage/oracle-l