-
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
-
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
-
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%';
-
Hi,
the output from that query is:
NAME VALUE
---------------------------------------------------------------- ---------
sorts (memory) 18091
sorts (disk) 9
sorts (rows) 29094566
thanks
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|