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

I may be missing something here, but it looks like you need to have an in-line procedure that will be used to compute your subquery.

Something like this:

function sum_procedure (masterID in number) Is
begin
select sum(price) from detailtable
where id = masterID
return sum
end;
 and use the PRAGMA to allow for inline function

Then your main query could reference it like this:

select sum_procedure(masterID)
from mastertable, table2,...
where conditions....

Hope this helps,
Harry

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.
Received on Sat Jun 24 2000 - 00:00:00 CDT