Re: how to compute value from different rows

On 3 dic, 11:53, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Maija-Leena" <kangasm..._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

Also, take a look at INTERVAL datatypes if you need the 'duration' properly defined.

HTH. Cheers.

Carlos. Received on Wed Dec 03 2008 - 06:15:53 CST