SQL Query optimization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Query optimization

Hybrid View

  1. #1
    Join Date
    Dec 2005
    Posts
    2

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  3. #3
    Join Date
    Dec 2005
    Posts
    2
    True but doesnt improve the performance time!

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about a composite index on (my_date,my_amount)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width