RE: Riddle me this.....
Date: Thu, 9 Apr 2009 16:24:08 -0400
Message-ID: <8833494F383585499CB855121711D26313919937B9_at_JBXEXVS02.fplu.fpl.com>
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
i0zX+ n{+i^ Received on Thu Apr 09 2009 - 15:24:08 CDT