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

Thanks for the suggestions,

unfortunately I think neither method suggested probably can quite do everything I can currently do with my subquery = calculated field scheme,

the way I describe the SQL allows conditions and sorts to be applied to the calculated field within the main query, and beyond that it is a fairly trivial matter of including many calculted fields. I've really got to decide if to keep this or risk having to update application code, which probably isn't that big-a-deal (yet).

Internet Junkie's suggestion of using procedural SQL functions could probably be slipped in with less rewrites unfortunately some preliminary tests a while back seemed to indicate that these functions tended to be slow, especially when the results set is large (I have my cake now, please don't tell me I can't eat it). However things may have improved with 8i which I've just installed.

I may try and come up with someway of switching between the two, or at least support both, but any suggestions are welcome.

Sorry if this post is a bit of a ramble, at least if totally incomprehensible to any one else it's helped cleared up some of my own ideas.

Thanks,

Tony.

In article <8j2u48$5hr$1_at_nnrp1.deja.com>,   Michael Bialik <michael_bialik_at_my-deja.com> wrote:
> 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.
>

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