Re: Lag function problem was: Never ending activity in temp file

> 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.

--
http://www.freelists.org/webpage/oracle-l