Re: query rewrite

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Wed, 30 Dec 2009 18:43:19 +0100
Message-ID: <411d50f60912300943n6663cb63rdcb870faa803b771_at_mail.gmail.com>

Kyle,
for one, I haven't seen CBO trying to optimize them in the way you're asking.
I don't think that it will do such a thing in the near future, as it is not easy to insure that the result will be consistent (i.e. be sure that the query always returns one row, and that the conversion to a join will not introduce wrong results, e.g. due to nulls handling, .....) Well at least that is how I see it, but CBO guys sure know better than me if it is feasible or not.

rgds

On Wed, Dec 30, 2009 at 4:33 PM, kyle Hailey <kylelf_at_gmail.com> wrote:

>
> 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 - 11:43:19 CST