Re: how to compute value from different rows

"Maija-Leena" <kangasmaki_at_netti.fi> wrote in message news:dJrZk.26$JA4.5_at_read4.inet.fi...
> Hi,
>
> I have a big table (about 5 million rows) of phases and I need to compute
> the duration of the phase. I'm doing it now with a cursor loop (select...
> order by key, date; update duration := (newrow.date - oldrow.date) ), but
> that takes almost 20 minutes (Oracle 10g2). I believe it's possible to do
> with one update, but I don't remember how ? It might also be slow to
> commit
> 5 million updates at a time, but I would like to try that.
>
>
> row 1: key1 date1 phase1 duration = 0
> row 2: key1 date2 phase2 duration=date2-date1
> row 3: key1 date3 phase3 duration=date3-date2
>
> row4: key2 date1 phase1 duration = 0
> row5: key2 date2 phase2 duration=date2-date1
> row6: key2 date3 phase3 duration=date3-date2
> row7: key2 date4 phase4 duration=date4-date3
>
> Thanks in advance !
>
> Maija-Leena
>
>

Look in the docs for analytic functions. eg lag etc. (also asktom.oracle.com and search for analytics, lots of examples) It will be faster to do 1 update on the 5 million than row by row. Jim Received on Wed Dec 03 2008 - 04:53:08 CST