RE: Riddle me this.....

  • From: "SHEEHAN, JEREMY" <Jeremy.Sheehan@xxxxxxx>
  • To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • Date: Thu, 9 Apr 2009 16:36:55 -0400

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

Other related posts:

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