Re: Optimising from subqueries to only pull back the relavent table

Hi.

 Try using one of the following:

1. Inline view: SELECT t1.* , SUM(DetailTable.Price) Cost FROM ( SELECT ... FROM MasterTable, Table2,... WHERE ... ) t1, DetailTable WHERE t1.MasterID = DetailTable.MasterID (+)
2. Regular join to DetailTable:

    SELECT ..., SUM(DetailTable.Price) Cost     FROM MasterTable, Table2,..., DetailTable     WHERE ( condition ) AND
     MasterTable.MasterID = DetailTable.MasterID (+)     GROUP BY ...  HTH. Michael

In article <8iv89o$43u$1_at_nnrp2.deja.com>,   a_p_day_at_my-deja.com wrote:
> Does anybody know if there is a way to get Oracle
> to take a querysomething like...
>
> Select * from
> MasterTable, Table2, ...,
> (Select MasterID as SQLink, Sum(Price) as Cost
> from DetailTable
> Group By MasterID)
>
> where {condition list}
> and SQLink (+)= MasterTable.ID
>
> and delay it's execution of the subquery such
> that it will not work out the sum till after the
> main conditions have been evaluated.
>
> Currently I have a system of calculated fields
> which is pretty fast for unrestrictive queries on
> the data but unfortuantely doesn't really gain
> any speed when restrictions are applied.
>
> I realise that the above query could probably be
> rewritten, but some of the more involved examples
> would be far more difficult to do generally.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jun 24 2000 - 00:00:00 CDT