order by
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: order by

  1. #1
    Join Date
    Jan 2001
    Posts
    10

    Question

    Hi,

    I've got a select query that returns almost instantly, but when I add an 'order by' on the largest table it takes 2+ hours.

    Is there any way of speeding up an 'order by'?

    thanks

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking


    run explain plan on it.
    if not using an index (which it probably is not) then create an index and it should run more smoothly.

    try that and see what happens.

    - Magnus

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by gareth
    Hi,

    I've got a select query that returns almost instantly, but when I add an 'order by' on the largest table it takes 2+ hours.

    Is there any way of speeding up an 'order by'?

    thanks
    Increase your SORT_AREA_SIZE if you sort a lot on disk.

    Run this as sys and giv ethe output:

    select name, value from v$sysstat where name like '%sort%';


  4. #4
    Join Date
    Jan 2001
    Posts
    10
    Hi,

    the output from that query is:

    NAME VALUE
    ---------------------------------------------------------------- ---------
    sorts (memory) 18091
    sorts (disk) 9
    sorts (rows) 29094566

    thanks


  5. #5
    Join Date
    Jul 2000
    Posts
    521
    well, I won't expect the sort to complete instataneously for 29 million rows !!

    If you are concerned about response time, try using FIRST_ROWS hint if its not going to have any other impact.
    svk

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by gareth
    Hi,

    the output from that query is:

    NAME VALUE
    ---------------------------------------------------------------- ---------
    sorts (memory) 18091
    sorts (disk) 9
    sorts (rows) 29094566

    thanks

    You are doing fine. No need to increase SORT_AREA_SIZE. What to do next.. Well, in SQL*PLUS type SET AUTOTRACE ON, run again the query and post the output.


  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    This post would be most appropriate in the development forum rather than in the administration side. So, I'm moving this thread from administration forum to the development forum.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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