Re: Lag function problem was: Never ending activity in temp file
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: Edgar Chupit <chupit@xxxxxxxxx>
- Date: Wed, 13 Apr 2005 14:57:54 +0300
> For your particular query you can tune it, by specifying partition
> clause, and thus limiting the size of the analytic window.=20
I'll try partition clause. And I tried a bit also lead function,
appeared it hadn't such problems (of course I can somehow order
everything vice versa and try lead).
> But I guess what you are really want to ask is: if for particular ID
> there is more than one row, give me the code of the first row, else
> give me value of current row.
this is oversimplified example just to show performance problems. The
real problem generally was, to count all codes for particular persons,
but in a following manner:
if person has code 'LVA' then count it and only it.
if person hasn't code 'LVA' then count all other codes particular person ha=
s.
So to avoid exists clause, self join or something similar I tried to
partition source table (actually join from many tables) by person id
and order by code in a manner that 'LVA' always comes first.
Then for each person I'll look back and see if this is new person,
then I count the code. If this is the same person as in previous row,
then I'll look back for this person first code, if it is 'LVA' then I
don't count code, else count it.
In this report I had to simply count codes, in other reports I'll have
to join them to other dimensions and count somehow even more complex
for example by address or sex.
And I see that here is the real power of analytic functions,
especially if they worked as I imagined :)))
> Like Tom Kyte says "Tune the question, not the query".=20
Of course that's true.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
