Re: theory of rewriting a query
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- Date: Wed, 13 May 2009 20:28:50 +1000
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@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
