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
Printable View
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
Increase your SORT_AREA_SIZE if you sort a lot on disk.Quote:
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
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.
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.Quote:
Originally posted by gareth
Hi,
the output from that query is:
NAME VALUE
---------------------------------------------------------------- ---------
sorts (memory) 18091
sorts (disk) 9
sorts (rows) 29094566
thanks
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