Re: query rewrite
- From: Ghassan Salem <salem.ghassan@xxxxxxxxx>
- To: kyle Hailey <kylelf@xxxxxxxxx>
- Date: Wed, 30 Dec 2009 18:43:19 +0100
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@xxxxxxxxx> 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@xxxxxxxxx>wrote:
>
>> On Wed, Dec 30, 2009 at 8:53 AM, Ghassan Salem <salem.ghassan@xxxxxxxxx>
>> 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
>>
>
>
Other related posts:
