-
SQL Query optimization
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.
-
Originally Posted by suneeshsnair
Code:
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
-
True but doesnt improve the performance time!
-
Originally Posted by suneeshsnair
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
-
How about a composite index on (my_date,my_amount)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|