how to compute value from different rows

From: Maija-Leena <kangasmaki_at_netti.fi>
Date: Wed, 03 Dec 2008 08:48:09 GMT
Message-ID: <dJrZk.26$JA4.5@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 Received on Wed Dec 03 2008 - 02:48:09 CST