SQL Query optimization

[suneeshsnair]

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.

[tamilselvan]

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

I think you do not need myamount in the order by clause.
Try this:
over (order by mydate rows unbounded preceding )

And see you get the same result set.

Tamil

[suneeshsnair]

True but doesnt improve the performance time!

[tamilselvan]

True but doesnt improve the performance time!

Use 10046 trace event to get more info on wait events.

The sort area size or temp file may not be set up correctly.

Tamil

[slimdave]

How about a composite index on (my_date,my_amount)