I've got one SQL query which 1) sorts my oracle table according to date and then amount
2) then it returns the first N records whose amount field sum equals to a predefined value (say 1000)
select myamount,mydate from (select mydate , myamount, sum (myamount) over (order by mydate,myamount rows unbounded preceding ) as sum_amount from mytable ) where sum_amount<= 1000
This query is currently taking 50 seconds to execute. I would like to know if this can be further optimized so that it will run in lesser time.