Re: query rewrite
From: Marcin Przepiorowski <pioro1_at_gmail.com>
Date: Wed, 30 Dec 2009 15:52:45 +0000
Message-ID: <3f5690af0912300752s3de4d23rf0f3eade74f437df_at_mail.gmail.com>
On Wed, Dec 30, 2009 at 3:33 PM, kyle Hailey <kylelf_at_gmail.com> wrote:
>>
>> 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.
>>
>
> 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?
Date: Wed, 30 Dec 2009 15:52:45 +0000
Message-ID: <3f5690af0912300752s3de4d23rf0f3eade74f437df_at_mail.gmail.com>
On Wed, Dec 30, 2009 at 3:33 PM, kyle Hailey <kylelf_at_gmail.com> wrote:
>>
>> 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.
>>
>
> 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?
Kyle,
Not really - my main point was that subquery in select has to return one row
and for some people it is very difficult to find a proper join and
filter condition
and some of them are using min or max function to limit number of rows to one.
In that case query will be always working fine.
Other problem is that these kind of query are sometimes very expensive
from resource point of view
and sometimes a simple rewrite or pushup a subquery to join section can help.
regards,
Marcin Przepiorowski
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 30 2009 - 09:52:45 CST