order 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

[jgmagnus]

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

[julian]

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%';

[gareth]

Hi,

the output from that query is:

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

thanks

[svk]

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.

[julian]

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.

[sambavan]

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