Re: Riddle me this.....

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: Jeremy.Sheehan@xxxxxxx
  • Date: Thu, 09 Apr 2009 22:04:11 +0200

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

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

Other related posts:

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