Re: theory of rewriting a query
Date: Wed, 13 May 2009 20:28:50 +1000
Message-ID: <4A0AA0E2.5080501_at_iinet.net.au>
Stephane Faroult wrote,on my timestamp of 13/05/2009 6:03 AM:
> Joel,
>
> What really kills in this type of quey is the fact that the
> subqueries are dependent on each other - for instance the second
> subquery depends on d.effdt that is determined by the first subquery.
> You have the same type of unsound relationship between subqueries 3 and
> 4. In fact, you want, so to speak, a "greater value of greater value "
> (in that case the greatest effseq for the greatest effdt), which usually
> calls for analytic functions as Ken Naim suggested.
>
> If you take the subqueries in isolation:
Yes, that is my observation as well.
The way I usually handle these is by merging the
pairs of subqueries into one, just like you propose.
The "greater of greater" then becomes a simpler "greater".
Easier said than done, though: as Dave pointed out, this is "canned" code from the PS query builder. Not always possible to get rid of or replace by simpler SQL.
The conversion to a view helps, but make sure the result of the query is not then going to be used in another view, and so on ad nauseum. That cure is much worse...
> At this stage you get two subqueries instead of four. Then you should
> ask yourself whether you couldn't, at the outer level, refer to each
> table once instead of twice, use an OR, multiple CASE ... END constructs
> and a GROUP BY to bring everything in a single pass - at which point you
> can perhaps reduce your two subqueries to one, partition by emplid and
> empl_rcd and so on ...
This is where things get hairy. Although extensive redefinition of the table(s) involved is an effective strategy, it has the drawback of making your PS implementation seriously upgrade-impaired: if you install a later release, you have to revisit all this work.
Many places throw that into the too hard basket and go with the simpler merge of two subqueries into one and use of a view if too hard to make query builder accept the result. Note also the _UNNEST_SUBQUERY mentioned by David: it is almost essential in PS HR and payroll, dunno about financials.
-- Cheers Nuno Souto dbvision_at_iinet.net.au -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 13 2009 - 05:28:50 CDT