Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Lag function problem was: Never ending activity in temp file

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

From: Gints Plivna <>
Date: Wed, 13 Apr 2005 14:57:54 +0300
Message-ID: <>

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

Received on Wed Apr 13 2005 - 08:01:56 CDT

Original text of this message