Re: query rewrite
Date: Wed, 30 Dec 2009 07:33:35 -0800
Would you say that these correlated sub queries in the select is are a BAD idea since no one has seen Oracle trying to push them into the FROM clause thus blocking efficient execution paths?
> On Wed, Dec 30, 2009 at 8:53 AM, Ghassan Salem <salem.ghassan_at_gmail.com>
> > Well, semantically, it cannot be pushed, as Kyle noted, if in the first
> > case, the select returns more than one row, it gives an error, in the
> > case, it gives more than one row, so, semantically, the queries are not
> > equivalent, and CBO cannot rewrite them. In all cases, I don't think it
> > tries to do so.
> You are right but I have seen a lot of "workarounds" for a bad written
> subqueries with
> min or max taken from ID or name just to achieve a single row return.
> Marcin Przepiorowski