Re: Riddle me this.....

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: "SHEEHAN, JEREMY" <Jeremy.Sheehan@xxxxxxx>
  • Date: Thu, 09 Apr 2009 22:26:35 +0200

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 @ 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@xxxxxxx
> (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@xxxxxxxxxxxx] 
> Sent: Thursday, April 09, 2009 4:04 PM
> To: SHEEHAN, JEREMY
> Cc: Taylor, Chris David; jeremy.schneider@xxxxxxxxxxxxxx; 
> oracle-l@xxxxxxxxxxxxx
> 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>

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2009