Re: query rewrite

From: kyle Hailey <kylelf_at_gmail.com>
Date: Wed, 30 Dec 2009 07:33:35 -0800
Message-ID: <6f373fd20912300733v44263022n87ba96ca622b39f8_at_mail.gmail.com>

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?

Best
Kyle Hailey
http://db-optimizer.blogspot.com/
<http://db-optimizer.blogspot.com/>
On Wed, Dec 30, 2009 at 1:41 AM, Marcin Przepiorowski <pioro1_at_gmail.com>wrote:

> On Wed, Dec 30, 2009 at 8:53 AM, Ghassan Salem <salem.ghassan_at_gmail.com>
> wrote:
> > 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
> second
> > 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
> even
> > 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.
>
> regards,
> Marcin Przepiorowski
>

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

Received on Wed Dec 30 2009 - 09:33:35 CST